Multiple Maximum Values ​​in a Request

Question

I know that the name doesn’t sound very descriptive, but this is the best I could come up with:

I have this table

  ID BDATE VALUE
 28911 04/14/2009 44820
 28911 04/17/2009 32,240
 28911 04/20/2009 30550
 28911 04/22/2009 4422587.5
 28911 04/23/2009 4441659
 28911 24/4/2009 7749594.67
 38537 04/17/2009 58280
 38537 04/20/2009 137240
 38537 4/22/2009 81098692
 38605 14/4/2009 2722368
 38605 04/20/2009 5600
 38605 04/22/2009 1625400
 38605 04/23/2009 6936575

which is actually a very complex query, encapsulated in the form, but now it is not.

I would like to have for each id a string containing the highest BDate. In this example, this will be the result.

  ID BDATE VALUE
 28911 24/4/2009 7749594.67
 38537 4/22/2009 81098692
 38605 04/23/2009 6936575

I have already tried

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

but then it returns all rows, because for each value the column value is different. This query was developed in Oracle v10, and I have the right to use only individual queries, and not create procedures.

Answer 1

We can use the multiplication columns in the 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 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 

It will be as effective as the analytics method proposed by Majkel (without self-linking, one pass through the data)

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

You can use INNER JOIN to filter 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 

Fingerprints:

 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 identifier that has multiple values ​​with the same bdate.

Answer 5

 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 6

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