Taking the record with the max date
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
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
.
A is the key, max(date) is the value, we might simplify the query as below:
SELECT distinct A, max(date) over (partition by A)
FROM TABLENAME
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