Top X selection from a recordset grouped by specific column

Question

Top X selection from a recordset grouped by specific column

M Jones

SSCommitted

Points: 1711
More actions
October 19, 2010 at 12:57 pm

#241883

I'm trying to figure out how I can do the following:
I need the top 5 records for each group of records from a recordset where each group of records is created by grouping together by a specific column. Make sense?
IOW - I need the top 5 projects for each region..... If I have 5 regions, I need 25 records, 6 regions, I need 30 records....
I'd like to build a stored proc or view to give me what I need but I'm having trouble finding a way to do it without wasting resources...
Any suggestions?
~mj

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

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

Lowell SSC Guru Points: 323546 More actions · Answer 1

row number is what you want in this case;the key to it the PARTITION BY, which groups the rows based on the selected columns.

here's an example; if you look at the data, it has as many as 5 rows per id...so you could filter it so that the column alias for the rownumber is <=3 to get the top 3 (or in your case top 5 per region)

select Row_number() over (PARTITION BY X.CHK_NO ORDER BY X.CHK_NO),X.*

From (

SELECT 1234 AS chk_no,'medc' AS ben_code UNION

SELECT 1234,'ss' UNION

SELECT 1234,'fica' UNION

SELECT 1234,'hlth' UNION

SELECT 1235,'medc' UNION

SELECT 1235,'ss' UNION

SELECT 1235,'fica' UNION

SELECT 1235,'hlth' UNION

SELECT 1235,'dent' UNION

SELECT 1236,'medc' UNION

SELECT 1236,'ss' UNION

SELECT 1236,'fica' UNION

SELECT 1236,'hlth' UNION

SELECT 1237,'medc' UNION

SELECT 1237,'ss' UNION

SELECT 1237,'fica' UNION

SELECT 1237,'hlth' UNION

SELECT 1237,'dent' UNION

SELECT 1237,'csup') X

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

M Jones SSCommitted Points: 1711 More actions · Answer 2

Thank You! I will give it a try! I knew someone would understand what I am trying to do!

Yes! This is going to work beautifully - Thank you again, Lowell!

~mj