Selecting the TOP x records within a group

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 2000 Forums

SQL Server Development (2000)

Selecting the TOP x records within a group

Author

Topic

alanlambert
Starting Member

26 Posts

Posted - 2004-05-18 : 06:28:43

I know you can use TOP x to retrieve the first x records from a table, but how do you get the first x records from within a group?

For example, if I had a table as follows:


Category     Ranking
==========   =======
Category 1      3
Category 1      5
Category 1      4
Category 1     10
Category 2      1
Category 2      5
Category 2      3
Category 3     20
Category 3      5
Category 3      7
Category 3     18
Category 3     15
Category 3     10
Category 3      5

and I want to select get the top 2 rankings for each category, I would want the output to be:


Category     Ranking
==========   =======
Category 1     10
Category 1      5
Category 2      5
Category 2      3
Category 3     20
Category 3     18

Many thanks for any help!

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-18 : 06:46:30

select *
from tbl t1
where Ranking in (select top 2 t2.Ranking from tbl t2 where t2.Category = t1.Category order by t2.Ranking desc)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

alanlambert
Starting Member

26 Posts

Posted - 2004-05-18 : 06:58:47

Many thanks.

Alan

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-05-18 : 07:50:24

There are better ways.

Jay White
{0}

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources