36

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?

ZygD
  • 22,092
  • 39
  • 79
  • 102
Revious
  • 7,816
  • 31
  • 98
  • 147

7 Answers7

75

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.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • 5
    @Gik25 - A tie would occur if there were, say, two rows in `TABLENAME` that had the same value for `A` and the same value for `SOME_DATE_COLUMN`. Your original query would return both of those rows as would mine. If, on the other hand, you used the `ROW_NUMBER` function, only one of the two rows would be returned (though the choice of which row to return would be arbitrary). – Justin Cave Jan 18 '12 at 14:45
24

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
chema989
  • 3,962
  • 2
  • 20
  • 33
Benoit
  • 76,634
  • 23
  • 210
  • 236
  • 5
    @Matt: Yes, but only if the OP wants just these 2 columns and not the whole row (as it is implied in the question). – ypercubeᵀᴹ Jan 17 '12 at 16:39
  • 1
    @ypercube I agree, and that is what the question looks like – Matt Donnan Jan 17 '12 at 16:41
  • 1
    I've couln't make third query work in my Oracle 11g. And second query is missing a **group by** clause. It should be: `WITH x AS ( SELECT A, MAX(col_date) m FROM TABLENAME **group by A**) SELECT A, date FROM TABLENAME t JOIN x ON x.A = t.A AND x.m = t.col_date` – EAmez Oct 08 '18 at 12:52
10

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
ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
  • 1
    This would be a good choice if there was an index on `(a, col_date)`, especially if there are lots of dates for each distinct value of A. – APC Jan 17 '12 at 18:17
4

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
ZygD
  • 22,092
  • 39
  • 79
  • 102
user2778168
  • 193
  • 9
1

Justin Cave answer is the best, but if you want antoher option, try this: 

select A,col_date
from (select A,col_date
    from tablename 
      order by col_date desc)
      where rownum<2
Aitor
  • 3,309
  • 2
  • 27
  • 32
1

Since Oracle 12C, you can fetch a specific number of rows with FETCH FIRST ROW ONLY. In your case this implies an ORDER BY, so the performance should be considered.

SELECT A, col_date
FROM TABLENAME t_ext
ORDER BY col_date DESC NULLS LAST
FETCH FIRST 1 ROW ONLY;

The NULLS LAST is just in case you may have null values in your field.

Nanorex
  • 11
  • 1
  • 3
-6
SELECT mu_file, mudate
  FROM flightdata t_ext
 WHERE mudate = (SELECT MAX (mudate)
                     FROM flightdata where mudate < sysdate)
josliber
  • 43,891
  • 12
  • 98
  • 133
Robert
  • 1