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