SELECT Groups of TOP # - 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)

SELECT Groups of TOP #

Author

Topic

rohcky
Starting Member

38 Posts

Posted - 2008-01-07 : 12:26:25

I was wondering if it's possible to select a group of top records.

ID count(*)
------------
1 15
1 14
1 13
1 12
2 15
2 14
2 13
2 12
2 11

I want to receive:

ID count(*)
------------
1 15
1 14
2 15
2 14

Is it possible to do that?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-07 : 12:50:51

SELECT tmp.ID,tmp.[Count] FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Count DESC) AS 'RowNo',
t.ID,
t.[Count]
FROM
(
SELECT ID,COUNT(*) AS 'Count'
FROM YourTable
GROUP BY ID
)t
)tmp
WHERE t.RowNo<=2

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-07 : 13:26:07

quote:
Originally posted by visakh16

SELECT tmp.ID,tmp.[Count] FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Count DESC) AS 'RowNo',
t.ID,
t.[Count]
FROM
(
SELECT ID,COUNT(*) AS 'Count'
FROM YourTable
GROUP BY ID
)t
)tmp
WHERE t.RowNo<=2

Almost... try:

select t.ID, t.Column1, t.Column2... from (
select row_number() over (partition by ID order by Column1, Column2...) as rownbr, ID, Column1, Column2... from YourTable
) t
where a.rownbr <=2

rohcky
Starting Member

38 Posts

Posted - 2008-01-07 : 13:45:03

Thank you both for the quick response. I tried jdaman's query and it worked. Thanks again.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-08 : 01:31:30

See what you can do with Row_number() function
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.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