Help with a SELECT quey !!! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with a SELECT quey !!!

Author

Topic

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2009-04-23 : 16:47:13

Hi All I have a table called table1.
It has following fields

ID
Date

Lets say the table has following records.

ID, Date
001,2007-01-05
001,2007-01-10
001,2007-01-09
002,2008-01-01
002,2008-01-03
002,2008-01-07
002,2008-01-08
002,2008-01-10
002,2008-01-20
002,2008-01-21

In this table each ID may have multiple dates. I want to get the each ID with the most recent 4 dates. So against the above example my SELECT should return the following.

ID, Date
001,2007-01-05
001,2007-01-10
001,2007-01-09
002,2008-01-08
002,2008-01-10
002,2008-01-20
002,2008-01-21

How can I achieve this, please reply ASAP.

Thanks,

Zee

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-23 : 16:48:55

SELECT ID, Date
FROM (
SELECT ID, Date, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Date DESC) AS recID
FROM Table1
) AS d
WHERE recID <= 4

E 12°55'05.63"
N 56°04'39.26"