Taking the record with the max date

Question

Let's assume I extract some set of data.

i.e.

SELECT A, date FROM table 

I want just the record with the max date (for each value of A). I could write

SELECT A, col_date   FROM TABLENAME t_ext  WHERE col_date = (SELECT MAX (col_date)                      FROM TABLENAME t_in                     WHERE t_in.A = t_ext.A) 

But my query is really long... is there a more compact way using ANALYTIC FUNCTION to do the same?

Answer 1

The analytic function approach would look something like

SELECT a, some_date_column   FROM (SELECT a,                some_date_column,                rank() over (partition by a order by some_date_column desc) rnk           FROM tablename)  WHERE rnk = 1 

Note that depending on how you want to handle ties (or whether ties are possible in your data model), you may want to use either the ROW_NUMBER or the DENSE_RANK analytic function rather than RANK.

Answer 2

If date and col_date are the same columns you should simply do:

SELECT A, MAX(date) FROM t GROUP BY A 

Why not use:

WITH x AS ( SELECT A, MAX(col_date) m FROM TABLENAME GROUP BY A ) SELECT t.A, t.date FROM TABLENAME t JOIN x ON x.A = t.A AND x.m = t.col_date 

Otherwise:

SELECT A, FIRST_VALUE(date) KEEP(dense_rank FIRST ORDER BY col_date DESC)   FROM TABLENAME  GROUP BY A