SQL: Returning the Top X row for each group (SQL Spackle) (good one)

Reference: http://www.sqlservercentral.com/articles/T-SQL/71571/

Returning the Top X row for each group (SQL Spackle)

By Dave Ballantyne, 2012/11/23 (first published: 2010/12/06)

In our table we have a list of runners, the time the course was completed in and the runner’s age. Our task is to retrieve the fastest X runners in each age range.

CREATE TABLE #Runners
(
Runner integer NOT NULL,
Time integer NOT NULL,
Age integer NOT NULL
)
INSERT INTO #Runners
SELECT 1 , 10 , 20 UNION ALL
SELECT 2 , 15 , 20 UNION ALL
SELECT 3 , 11 , 20 UNION ALL
SELECT 4 , 12 , 30 UNION ALL
SELECT 5 , 18 , 30 UNION ALL
SELECT 6 , 9 , 40 UNION ALL
SELECT 7 , 16 , 40 UNION ALL
SELECT 8 , 13 , 30

By far the simplest way is to use a combination of the ranking functions ( Specifically row_number () ) and CTE ‘s (Common Table Expressions). This may not always be the most efficient, but I’ll come back to that point later.

Our first task is to ascertain the order that the runners completed the course in. If we were interested in only the overall, age range aside, order we could simply

Select * 
from #Runners 
order by Time 

and then by adding then TOP clause we can filter the Top X (assuming 2 here)

Select top(2) * 
from #Runners 
order by Time 

However, we do need the TOP X for each age category. As mentioned above we will be using the row_number() ranking function to help us do this. If we were to execute

select * ,row_number() over (order by Time ) as RowN 
from #Runners 
order by Rown 
Runner Time Age RowN
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s