Taking the record with the max date
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
.
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
You could also use:
SELECT t.* FROM TABLENAME t JOIN ( SELECT A, MAX(col_date) AS col_date FROM TABLENAME GROUP BY A ) m ON m.A = t.A AND m.col_date = t.col_date