Get rows for latest date

Hello!

I have a table something like this:

ID INTEGER

Info VARCHAR (actually several columns but that is not important here)

DAT DateTime

For each ID there are several dates and for each of these dates there are several rows with different info. I would like to select the latest info for each ID. For example:

ID - DAT - Info

1 - 2007-02-01 - Info1

1 - 2007-02-01 - Info2

1 - 2006-02-01 - Info3

2 - 2007-05-05 - Info4

2 - 2007-02-01 - Info5

2 - 2006-02-01 - Info6

I would like to get:

Info1

Info2

Info4

This has to be done in one Query. Can anybody help me?

Here it is,

Code Block

Create Table #data (

[ID] int ,

[DAT] datetime ,

[Info] Varchar(100)

);

Insert Into #data Values('1','2007-02-01','Info1');

Insert Into #data Values('1','2007-02-01','Info2');

Insert Into #data Values('1','2006-02-01','Info3');

Insert Into #data Values('2','2007-05-05','Info4');

Insert Into #data Values('2','2007-02-01','Info5');

Insert Into #data Values('2','2006-02-01','Info6');

select main.info from #data main

join (select ID,max(dat) dat from #data group by ID) as latest

on latest.ID=main.ID and latest.dat=main.dat

|||

A couple of options:

Code Block

create table testdata

(ID int, Dat DATETIME, Nm CHAR(5))

INSERT INTO testdata

SELECT 1, '1 feb 2007', 'Info1'

UNION ALL

SELECT 1, '1 feb 2007', 'Info2'

UNION ALL

SELECT 1, '1 feb 2006', 'Info3'

UNION ALL

SELECT 2, '5 may 2007', 'Info4'

UNION ALL

SELECT 2, '1 feb 2007', 'Info5'

UNION ALL

SELECT 2, '1 feb 2006', 'Info6'

--SQL2005

WITH cte

AS

(SELECT ID, Nm, RANK() OVER (PARTITION BY ID ORDER BY Dat DESC) AS D

FROM testData)

SELECT Nm

FROM cte

WHERE D = 1

--SQL2000

SELECT Nm

FROM

(SELECT ID, MAX(Dat) AS Dt

FROM testData

GROUP BY ID) AS Bob

INNER JOIN testData t

ON Bob.Dt = t.Dat AND Bob.ID = t.ID

HTH!|||

One more trick..

Code Block

--SQL2005

;with cte

as

(select id, nm,dat,max(dat) over (partition by id) as latestdat from testdata)

select nm

from cte

where dat = latestdat

Code Block

--SQL Server 2000

select

main.info

from

#data main

where

exists

(

select * from

(

select

d

,max(dat) dat

from

#data

group by D

) data

where

data.d = main.d

and data.dat=main.dat

)

|||

Thank You guys!!

...for the fast and helpful response. I would never figure that out.