return top n records by type - 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)

return top n records by type

Author

Topic

demifelix
Starting Member

22 Posts

Posted - 2007-10-12 : 15:40:59

Hi,

Sample table:
Year Amount
2000 100
2000 200
2000 300
2000 400
2001 400
2001 500
2001 600
2001 700

I have data in a format above, and I want to return the top 3 records for each year so that the results are:

Year Amount
2000 400
2000 300
2000 200
2001 700
2001 600
2001 500

Thanks for your help.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-12 : 15:52:38

There's a sticky post on the "new to sql server" section. check it out.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

demifelix
Starting Member

22 Posts

Posted - 2007-10-12 : 20:23:36

I tried looking in that area, but there are 299 pages and I couldn't find what I was looking for. Could you please post the link? Thanks.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-12 : 23:34:15

quote:
Originally posted by demifelix

I tried looking in that area, but there are 299 pages and I couldn't find what I was looking for. Could you please post the link? Thanks.

There's only 3 pages: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

demifelix
Starting Member

22 Posts

Posted - 2007-10-13 : 00:43:53

Thanks. I look at that sticky and some other posts, and I took some ideas and applied but they don't work in my situation. I also found out that I actually run in SQL Server 2000 environment, so I post again in the correct forum. If you have time please help me out. I'm new to SQL Server and very desperate. I spent hours on this thing already and didn't get it working yet.
Here's the link of my post. Please help.
[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=90934[/url]
Thanks again.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-10-13 : 00:52:31

in sql 2005 it's very easy.


select *
from
(
select Row_Number() Over (Partition by year, order by amount desc) as RowID,*
from mytable
) a
where a.RowID <= 3

Basically the thought process is you need to rank the rows then take the top 3 for each. This is the same methodolgy in sql 2000, just more complicated

the below article might help out
http://www.codeproject.com/useritems/Rank_Query.asp

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-13 : 12:16:19

Awright...


SELECT [year], Amount
FROM YourTable T
WHERE (
	SELECT count(*) 
	FROM YourTable T2
	WHERE T2.[Year] = T.[Year] AND T2.Amount > T.Amount
	) <= 2
ORDER BY [Year] ASC, Amount DESC

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

demifelix
Starting Member

22 Posts

Posted - 2007-10-17 : 09:07:41

Thanks for looking into this dinakar. I posted my comments over in my other thread (SQL server 2000 section).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources