Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Top n of grouped data
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bijan
Starting Member

3 Posts

Posted - 04/09/2015 :  09:13:06  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3873 Posts

Posted - 04/09/2015 :  15:30:59  Show Profile  Reply with Quote
SELECT TOP (5)
   Skills
FROM
   DepartmentTable
GROUP BY
   Skills
ORDER BY
   COUNT(Transactions) DESC
If that is not what you are looking for post some sample data.
Go to Top of Page

bijan
Starting Member

3 Posts

Posted - 04/10/2015 :  04:24:45  Show Profile  Reply with Quote
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,


Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22864 Posts

Posted - 04/10/2015 :  08:02:55  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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

Edited by - madhivanan on 04/10/2015 08:03:43
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3873 Posts

Posted - 04/10/2015 :  08:19:43  Show Profile  Reply with Quote
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
    )
Go to Top of Page

bijan
Starting Member

3 Posts

Posted - 04/10/2015 :  10:47:06  Show Profile  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22864 Posts

Posted - 04/13/2015 :  06:22:09  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000