Top N of grouped data - 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 2000 Forums

Transact-SQL (2000)

Top N of grouped data

Author

Topic

parand
Starting Member

8 Posts

Posted - 2007-09-30 : 08:52:14

I would to return top N of a grouped data.
Would anyone tell me how. Say a and b are the grouped data sorted by some value as below:

a 100
a 90
a 80
a 70
a 60
b 100
b 90
b 80
b 70
b 60

I would like to return say only the top 3 of the grouped data as below:

a 100
a 90
a 80
b 100
b 90
b 80

Obviuosly N will be different and the grouped data might be different columns.

Your advice id appreciated.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-30 : 09:24:00

[code]DECLARE @sample TABLE
(
col1 CHAR(1),
col2 int
)
INSERT INTO @sample
SELECT 'a', 100 UNION ALL
SELECT 'a', 90 UNION ALL
SELECT 'a', 80 UNION ALL
SELECT 'a', 70 UNION ALL
SELECT 'a', 60 UNION ALL
SELECT 'b', 100 UNION ALL
SELECT 'b', 90 UNION ALL
SELECT 'b', 80 UNION ALL
SELECT 'b', 70 UNION ALL
SELECT 'b', 60

SELECT *
FROM @sample s
WHERE col2 IN (SELECT TOP 3 col2 FROM @sample x WHERE x.col1 = s.col1 ORDER BY col2 DESC)

/*
col1 col2
---- -----------
a 100
a 90
a 80
b 100
b 90
b 80
*/[/code]

[EDIT] : missed out the ORDER BY

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

parand
Starting Member

8 Posts

Posted - 2007-09-30 : 12:01:37

Thanks very much for this. But can this be done without creating a table and besides the coulmns have thousands of entries which make it virtually impossible to use the UNION keyword.

parand

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-30 : 15:37:14

a table is just for sample data.
look at the query to see the principle used.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-10-01 : 16:47:11

Here is another method using a JOIN for comparison:

SELECT 
	a.col1, 
	a.col2 
FROM 
	@sample a
INNER JOIN
	@sample b
	ON a.col1 = b.col1
WHERE 
	a.col2 <= b.col2
GROUP BY 
	a.col1, 
	a.col2
HAVING 
	COUNT(*) BETWEEN 1 AND 3
ORDER BY 
	a.col1,
	a.col2 DESC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources