top 5 in each group

Question

Post reply

top 5 in each group

radekhatle

SSC Eights!

Points: 955
More actions
August 22, 2010 at 5:27 am

#225051

Hi I have table with these column cw,q_number,count
I need top5 count in each group.
I use this query:
SELECT T2.cw, T2.q_number, T2.pocet AS count
FROM dbo.v_report_top5_questation AS T1 INNER JOIN
dbo.v_report_top5_questation AS T2 ON T1.cw = T2.cw AND T1.pocet >= T2.pocet
GROUP BY T2.cw, T2.q_number, T2.pocet
HAVING (COUNT(*) <= 5)
But there is problem for example:
cw,q_number,count
1,1,3
1,5,2
1,2,7
1,3,9
1,4,2
1,6,2
1,10,2
it returns only 3 rows
1,1,3
1,2,7
1,3,9.
thx Radek

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

check out ROW_NUMBER() using the partition by clause in books online.

Keep in mind that the new column is only available in the where clause after going to a derived table :

SELECT * FROM (

SELECT Whatever, ROW_NUMBER() AS GroupRowID... FROM ....

) AS dta

WHERE dta.GroupRowID <= 5

WayneS SSC Guru Points: 95460 More actions · Answer 2

radek-902543 (8/22/2010)
Hi I have table with these column cw,q_number,count

You know, the people that help out here are all un-paid volunteers, so please HELP US HELP YOU. Providing the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables that shows your problem will go a long way in getting people to look at your issue and help you out. Please include code for what you have already tried. Don't forget to include what your expected results should be, based on the sample data provided. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the first link in my signature.

I need top5 count in each group.

Which column is the "group" that you are wanting to count against? Again, the sample data and your expected results based on this would help.

It does sound like the ROW_NUMBER() function, with the OVER (PARTITION BY <groupcolumn>, ORDER BY <ordercolumn>) is what you need, as Ninja's_RGR'us suggested.

If this doesn't get you what you need, then you need to follow the how to post advice above to get people to help you out.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

radekhatle SSC Eights! Points: 955 More actions · Answer 3

Hi,

thanks Next topic will be better published:)

I used Over clausule vith partition and get it result.

thx

Radek