Search code examples
oracledatemaxanalytic-functions

Taking the record with the max date

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?

Solution

  • 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.