top 3 records for each id - 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

General SQL Server Forums

New to SQL Server Programming

top 3 records for each id

Author

Topic

Vadivu
Starting Member

31 Posts

Posted - 2009-09-23 : 05:56:01

I have a history table where records are stored as follows
Code EffDate
6789 9/23/2009
6789 9/20/2009
6789 8/21/2009
6789 7/14/2009
1353 9/23/2009
1353 9/20/2009
1353 8/14/2009
1353 8/7/2009

how to write a query to get the recent 3 effdates for each record?
I need an output as follows
Code EffDate
6789 9/23/2009
6789 9/20/2009
6789 8/21/2009
1353 9/23/2009
1353 9/20/2009
1353 8/14/2009

Can anybody help me? thanks in advance.

Vadivu
Starting Member

31 Posts

Posted - 2009-09-23 : 06:12:49

got the answer from the following blog:
http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx

Select EffDate, Code
From HST as A
Where (Select count(1) From HST Where Code =A.Code and EffDate<= A.EffDate)<=3
Order By Code , EffDate Desc

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-23 : 06:55:56

also refer to http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

KH
[spoiler]Time is always against us[/spoiler]

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-09-23 : 06:57:22

This method is somewhat old...here is a newer version compatible with 2005 and above:

Select EffDate, Code
From HST as A
Where (row_number() over partition by Code order by EffDate desc) <= 3
Order By Code , EffDate Desc

- Lumbago

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-23 : 08:03:17

quote:
Originally posted by Lumbago

This method is somewhat old...here is a newer version compatible with 2005 and above:

Select EffDate, Code
From HST as A
Where (row_number() over partition by Code order by EffDate desc) <= 3
Order By Code , EffDate Desc

- Lumbago

I doubt you can use windows functions directly in the where clause

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-23 : 08:05:32

quote:
Originally posted by Vadivu

I have a history table where records are stored as follows
Code EffDate
6789 9/23/2009
6789 9/20/2009
6789 8/21/2009
6789 7/14/2009
1353 9/23/2009
1353 9/20/2009
1353 8/14/2009
1353 8/7/2009

how to write a query to get the recent 3 effdates for each record?
I need an output as follows
Code EffDate
6789 9/23/2009
6789 9/20/2009
6789 8/21/2009
1353 9/23/2009
1353 9/20/2009
1353 8/14/2009

Can anybody help me? thanks in advance.

Also refer
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/09/12/return-top-n-rows.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources