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

We can use multiply columns in an IN clause:

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

Answer 2

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)