Multiple max values in a query

Question

I know the title does not sound very descriptive, but it is the best I could think of:

I have this table

ID     BDATE      VALUE
28911  14/4/2009  44820
28911  17/4/2009  32240
28911  20/4/2009  30550
28911  22/4/2009  4422587,5
28911  23/4/2009  4441659
28911  24/4/2009  7749594,67
38537  17/4/2009  58280
38537  20/4/2009  137240
38537  22/4/2009  81098692
38605  14/4/2009  2722368
38605  20/4/2009  5600
38605  22/4/2009  1625400
38605  23/4/2009  6936575

which is in fact a very complicated query encapsulated in a view, but it is not of the matter now.

I would like to have for each ID, the row containing the highest BDate. In this example, this would be the result.

ID     BDATE      VALUE
28911  24/4/2009  7749594,67
38537  22/4/2009  81098692
38605  23/4/2009  6936575

I have already tried

select id, max(bdate), value from myview group by id, value

but then it returns all the rows, because for each the value collumn is different. This query is designed in Oracle v10, and I am eligible to use only select queries and not to create procedures.

Answer 1

select a.* from myview a, (select id, max(bdate) from myview group by id) b
where a.id = b.id and a.bdate = b.bdate

Answer 2

SELECT id, bdate, value FROM myview
 WHERE (id, bdate) IN (SELECT id, MAX(bdate) FROM myview GROUP BY id)

(untested... I don't have Oracle available right now...)

Answer 3

You can use analytics:

select 
      id, bdate, value 
    from
      (
        select
          id, bdate, value, max( bdate ) over ( partition by id ) max_bdate
        from
          myview
      )
    where
      bdate = max_bdate

Answer 4

select id, bdate, value 
from myview 
where (id, bdate) in
    (select id, max(bdate)
     from myview group by id)
/

Answer 5

You can use an INNER JOIN to filter out only the maximum rows:

select t.*
from YourTable t
inner join (
     select id, max(bdate) as maxbdate
     from YourTable
     group by id
) filter
    on t.id = filter.id
    and t.bdate = filter.maxbdate

This prints:

id     bdate       value
38605  2009-04-23  6936575
38537  2009-04-22  81098692
28911  2009-04-24  7749594.67

Note that this will return multiple rows for an id which has multiple values with the same bdate.

Answer 6

Hi kurast,

you can use the MAX...KEEP(DENSE_RANK FIRST...) construct:

SQL> SELECT ID,
  2         MAX(bdate) bdate,
  3         MAX(VALUE) KEEP(DENSE_RANK FIRST ORDER BY bdate DESC) VALUE 
  4   FROM DATA
  5  GROUP BY ID;

        ID BDATE            VALUE
---------- ----------- ----------
     28911 24/04/2009  7749594,67
     38537 22/04/2009    81098692
     38605 23/04/2009     6936575

This will be as efficient as the analytics method suggested by Majkel (no self-join, a single pass on the data)