Multiple max values in a query

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)/

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)

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

select t.*from YourTable tinner 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       value38605  2009-04-23  693657538537  2009-04-22  8109869228911  2009-04-24  7749594.67

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