Grouping

nbozic · #1 (**permalink**) 03-30-04, 11:48

Hi,

I tried many times but I couldn't come up with a query that will yield records of the most recent date for each ID.

The starting table TBL_DATE that I will use in the query, for example:
___________
ID | Date
------------
01 | 12/01
01 | 12/05
02 | 12/08
03 | 12/10
03 | 12/11
03 | 12/16
___________

The desired recordset returned (most recent date for each ID):

___________
ID | Date
------------
01 | 12/05
02 | 12/08
03 | 12/16
___________

I tried different techniques using GROUP BY and MAX(), but nothing worked so far... Any ideas?

Thanks,

NB

Damian Ibbotson · #2 (**permalink**) 03-30-04, 12:10

This is one way...

Code:

select id
,        date
from  (
          select id
           ,        date
           ,        rank() over (partition by id order by date desc nulls last) rank
           from  tbl_date
         ) ranked_dates
where rank = 1
;