SQL Server Top N per group row_number Single Table

 in SQL Server  tagged top / over / row_number / window / raptors by

This post provides a solution to the classic top n per group task where you have a single table to work with. Suppose you have a list of people and quantities. The people appear many times in the table. This could be employees and the dollar values of each of their sales, students and their marks for each of the courses they took or basketball players and the points they got in each game they played, or just about anything else you could imagine.

If you are working with 2 tables in a one-to-many relationship you can consider using the APPLY operator as described in the post SQL Server Cross Apply General Form.

Sports Example – Toronto Raptors

In this example we want to look at each players highest points games. To keep the list fairly short we will only look at their best 2 games, in terms of points scored, and only show results where the points scored are above 22. We will use a window function to get this done. We are asking for the top 2 points-levels for each player. 

with cte as 
(
-- for each player, the highest points
select row_number() over(
 partition by Player
 order by pts desc) as ptsrank,
 Player, pts
 from BoxScores
)
-- only the top 2 games and just to keep the
-- results few, only if over 22 points
select Player, pts, ptsrank from cte
where ptsrank < 3 and pts > 22
order by pts desc

Here is a screenshot of our results in SSMS. By the way, this data covers the Toronto Raptors season from October 19, 2017 to February 14, 2018. This is just one way of looking at the data. From this we might surmise that the top three offensive players are DeRozen, Lowry and Valenciunas in that order.

Assists worth one point

What if assists were worth a point? I do this to introduce a computed column based on points plus assists. Besides, it seems reasonable to reward playmaking as well. Also the query below uses TOP in the outer query to restrict the number of results. 

with cte as 
(
select row_number() over(
 partition by Player
 order by (pts + ast) desc) as ptsplusasstrank,
 Player, pts, ast, (pts+ast) as ptsasst
 from BoxScores
)
select top (9) Player, pts, ast, ptsasst, ptsplusasstrank from cte
where ptsplusasstrank < 3 
order by pts desc

The results of the above query are shown below. Our results didn’t change much by granting one point for an assist. Position 6 and 7 switched.