1. SQL Recipes
2. Calculations Per Group
3. Calculating Top N items per Group

Calculating Top N items per Group in SQL

How to find the top / bottom "N" rows in each group?

This question is more commonly seen that you think:

• Out of a list of twitter users, find their N recent most tweets
• Out of all our salespeople, find the top N performing ones by closed business in each region
• Out of all the cities, find the top N cities in each country by population

Conside a table cities with three columns country, city and population:

Top N without Grouping

We have 9 cities in our table, let's select the top 3 without grouping.

select country, 
       city,
       population 
from cities 
order by population desc 
limit 3;

Top N with Grouping with row_number()

Fortunately, we have access to the window function row_number() that numbers the current row within its partition (group), counting at 1.

When we don't specify any partition:

select country, 
       city,
       population, 
       row_number() over (order by population desc) as country_rank           
from cities;

When we create a partition for each country:

select country, 
       city,
       population, 
       row_number() over (partition by country order by population desc) as country_rank 
from cities;

Almost there!

Now to select the top 2 cities from each country, we wrap the above query in another query and filter the row on the country_rank column.

select * from (
    select country, 
           city,
           population, 
           row_number() over (partition by country order by population desc) as country_rank 
    from cities) ranks
where country_rank <= 2;