| 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 |
|
|
|
|
Top n of grouped data