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