Finding most current date of several records?

microsoft.public.sqlserver.programming

25 Aug 2006 2:48 PM

VMI

I have these records and I want to retrieve the most current date of all the
unique IDs:
ID           Date
001         1/1/2001
001         1/1/2002
001         1/1/2004   -- Returned
002         9/3/2002
002         9/5/2006   -- Returned
003         7/8/2004
003         8/5/2005   -- Returned

What query will return the following rows?
001         1/1/2004
002         9/5/2006
003         8/5/2005

Thanks.

25 Aug 2006 2:57 PM

SQL Menace

change table1 to your table name

select t1.ID,t1.date from (select ID,MAX(date) as MaxDate
from table1 group by ID) t2
join table1 t1 on t2.id =t1.id and t2.MaxDate =t1.Date

Denis the SQL Menace
http://sqlservercode.blogspot.com/

VMI wrote:
Show quote

> I have these records and I want to retrieve the most current date of all the
> unique IDs:
> ID           Date
> 001         1/1/2001
> 001         1/1/2002
> 001         1/1/2004   -- Returned
> 002         9/3/2002
> 002         9/5/2006   -- Returned
> 003         7/8/2004
> 003         8/5/2005   -- Returned
>
> What query will return the following rows?
> 001         1/1/2004
> 002         9/5/2006
> 003         8/5/2005
>
> Thanks.