Oracle select last record for a particular id

Question

I have a table in oracle 11g like this:

id   date  
---  ---
1    1-jun
1    2-jun
1    3-jun
2    1-jul
2    2-jul
2    3-jul

I am trying to extract the latest record corresponding to each id. I tried group by, max but I cannot get it to work. What I want is:

   id    date 
   ---   ---
    1    3-jun
    2    3-jul

What is the column type for your date? `SELECT id, MAX(date) FROM table GROUP BY id` works for me. — Kermit, Jul 11 '12 at 19:29
It is varchar2 but when i do: SELECT id, MAX(to_date(date,'yyyy-mm-dd') FROM table GROUP BY id; i get multiple records per id. Not the latest one. — Victor, Jul 11 '12 at 20:35
I found it here: http://stackoverflow.com/questions/612231/how-can-i-select-rows-with-maxcolumn-value-distinct-by-another-column-in-sql Thank you for your replies — Victor, Jul 11 '12 at 20:47

score 6 · Answer 1 · answered Jul 11 '12 at 19:26

6

Try this:

SELECT id, MAX(date)
  FROM <YOUR-TABLE>
 GROUP BY id

answered Jul 11 '12 at 19:26

Chandu

score 0 · Answer 2 · edited Feb 25 '14 at 06:41

0

try this one

'SELECT *FROM (SELECT * FROM table ORDER BY date   DESC )tmp GROUP BY Id ';

edited Feb 25 '14 at 06:41

answered Feb 25 '14 at 06:17

2 Answers2