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

bijan
Starting Member

3 Posts

Posted - 2015-04-09 : 09:13:06

Hi,
I need help.
Does anyone know how to return say, top 5 of a grouped data returned by sql sattement?
For example, I have written a SQL statement which returns number of transactions for say various skills within a department but I would like to return the top 5 of those skills in the department.

Many thanks,

Bijan

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2015-04-09 : 15:30:59

[code]SELECT TOP (5)
Skills
FROM
DepartmentTable
GROUP BY
Skills
ORDER BY
COUNT(Transactions) DESC[/code]If that is not what you are looking for post some sample data.

bijan
Starting Member

3 Posts

Posted - 2015-04-10 : 04:24:45

Thanks very much for your reply, but this is not what I want. Please have a look at below. This is a list of Codes and Trans No

BCT1 413
BCT1 277
BCT1 236
BCT1 82
BCT1 69
BCT1 68
BCT1 67
BCT1 67
BCT1 63
BCT1 55
BCT1 51
BCT1 50
BCT1 50
BCT1 45
BCT1 42
BCT1 41
BCT1 38
BCT1 35
BCT1 34
BCT1 31
BCT1 30
BCT1 27
BCT1 26
BCT1 24
BCT1 24
BCT1 23
BCT1 22
BCT1 22
BCT1 22
BCT1 22
BCT1 21
BCT1 19
BCT1 19
BCT1 18
BCT1 18
BCT1 17
BCT1 17
BCT1 17
BCT1 17
BCT1 17
BCT1 17
BCT1 17
BCT1 16
BCT1 16
BCT1 16
BCT1 16
BCT1 16
BCT1 15
BCT1 15
BCT1 15
BCT1 15
BCT1 15
BCT1 14
BCT1 14
BCT1 14
BCT1 14
BCT1 14
BCT1 14
BCT1 14
BCT1 14
BCT1 13
BCT1 13
BCT1 13
BCT1 12
BCT1 12
BCT1 12
BCT1 12
BCT1 12
BCT1 12
CSS1 18
CSS1 7
CSS1 7
CSS1 5
CSS1 5
CSS1 5
CSS1 4
CSS1 3
CSS1 3
CSS1 3
CSS1 3
CSS1 3
CSS1 3
CSS1 2
CSS1 2
CSS1 2
CSS1 2
CSS1 2
CSS1 2
CSS1 2
CSS1 2
CSS1 2
CSS1 2
CSS1 2
CSS1 2
CSS1 2
CSS1 2
CSS1 2
CSS1 2
CSS1 2
MED1 23
MED1 21
MED1 21
MED1 20
MED1 18
MED1 18
MED1 18
MED1 17
MED1 17
MED1 15
MED1 14
MED1 14
MED1 14
MED1 14
MED1 13
MED1 13
MED1 13
MED1 12
MED1 12
MED1 12
MED1 12
MED1 12
MED1 11
MED1 11
MED1 11
MED1 10
MED1 10
MED1 10
MED1 10
MED1 10
MED1 10
MED1 10
MED1 10
MED1 9
MED1 9
MED1 8
MED1 7
MED1 7
MED1 7
MED1 7

I want to return the following:

BCT1 413
BCT1 277
BCT1 236
BCT1 82
BCT1 69
BCT1 68
BCT1 67
BCT1 67
BCT1 63
BCT1 55
CSS1 18
CSS1 7
CSS1 7
CSS1 5
CSS1 5
CSS1 5
CSS1 4
CSS1 3
CSS1 3
CSS1 3
MED1 23
MED1 21
MED1 21
MED1 20
MED1 18
MED1 18
MED1 18
MED1 17
MED1 17
MED1 15

which is top 10 for each of the codes.
I hope this makes sense!

many thanks,

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2015-04-10 : 08:02:55

select col1,col2 from
(
select col1,col2, row_number() over (partition by col1 order by col2 desc) as sno from table
) as t
where sno<=10

Madhivanan

Failing to plan is Planning to fail

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2015-04-10 : 08:19:43

Madhivanan's suggestion would be what I would do as well, but the ROW_NUMBER function is available only in SQL 2005 or later. Since you posted to SQL 2000 forum, you are perhaps on SQL 2000. In that case, you will need to do something like this (untested - i don't have a sql 2000 installation)

SELECT
	code,
	TransNo
FROM
	table a
WHERE
	TransNo IN 
	( SELECT TOP 10 TransNo FROM table b
	WHERE a.code = b.code
	ORDER BY TransNo DESC
    )

bijan
Starting Member

3 Posts

Posted - 2015-04-10 : 10:47:06

Thank you very much both for the suggestions.
I have not tried them yet, but I will do next week and will definitely let you know.
Thank you again for taking the time to come up with solutions.

Regards,

Bijan

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2015-04-13 : 06:22:09

quote:
Originally posted by James K

Madhivanan's suggestion would be what I would do as well, but the ROW_NUMBER function is available only in SQL 2005 or later. Since you posted to SQL 2000 forum, you are perhaps on SQL 2000. In that case, you will need to do something like this (untested - i don't have a sql 2000 installation)
SELECT
code,
TransNo
FROM
table a
WHERE
TransNo IN
( SELECT TOP 10 TransNo FROM table b
WHERE a.code = b.code
ORDER BY TransNo DESC
)

Thanks I did not notice it was posted in 2000 forum. Here are some other methods http://beyondrelational.com/modules/2/blogs/70/posts/10845/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