How to get top n results in each ‘group by’ group in SQL?

Problem

You have a table with multiple records. For each distinct value in one of the columns (i.e., each ‘group by’ group), you want to retrieve the top ‘n’ rows based on some criteria.

In the below example, get the top 2 entries for each sales person based on ‘amount’ column.

Input

id	salesperson	amount	sale_date
1	John	100.00	2023-09-10
2	John	200.00	2023-09-15
3	John	50.00	2023-09-18
4	Jane	150.00	2023-09-11
5	Jane	300.00	2023-09-14
6	Doe	400.00	2023-09-10
7	Doe	100.00	2023-09-16

Try Hands-On: Fiddle

Create Input Table: Gist

Desired Output

salesperson	amount	sale_date
Doe	400.00	2023-09-10
Doe	100.00	2023-09-16
Jane	300.00	2023-09-14
Jane	150.00	2023-09-11
John	200.00	2023-09-15
John	100.00	2023-09-10

Solution 1:

Using Window Functions

    WITH RankedSales AS (
        SELECT
            salesperson,
            amount,
            sale_date,
            ROW_NUMBER() OVER(PARTITION BY salesperson ORDER BY amount DESC) AS rn
        FROM sales
    )

    SELECT salesperson, amount, sale_date
    FROM RankedSales
    WHERE rn <= 2;

Explanation:

ROW_NUMBER() OVER(PARTITION BY salesperson ORDER BY amount DESC) – This assigns a unique sequential integer to rows within a partition of a result set.

We use a Common Table Expression (CTE) namedRankedSales to make our main query simpler.

In the main query, we filter out the results to only include rows where the row number (rn) is 2 or les
ns.

Solution 2:

Using Correlated sub query

SELECT s1.salesperson, s1.amount, s1.sale_date
FROM sales s1
WHERE (
    SELECT COUNT(*) 
    FROM sales s2 
    WHERE s1.salesperson = s2.salesperson AND s2.amount >= s1.amount
) <= 2
ORDER BY s1.salesperson, s1.amoun DESC;

Explanation:

The outer query iterates over each row in the sales table aliasing it as s1.

The inner query (correlated subquery) counts how many records have an amount greater than or equal to the current record for the same salesperson.

The condition of <= 2 in the outer query is used to limit the results to only the top 2 sales for each salesperson.

The main drawback of this method is that the correlated subquery will execute for each row in the sales table, which could be inefficient for larger datasets.

Still, this method provides a way to achieve the desired result without relying on window functions or session variables, making it useful in certain scenarios or older versions of databases that don’t support more advanced features.

Recommended Courses

1. SQL for Data Science – Level 1
2. SQL for Data Science – Level 2
3. SQL for Data Science – Level 3

Recommended Tutorial

1. Introduction to SQL
2. SQL Window Functons – Made Simple and Easy
3. SQL Subquery

More SQL Questions

1. How to concatenate multiple rows into one field in SQL?
2. How to efficiently convert rows to columns in SQL?
3. How to transpose columns to rows in SQL?
4. How to select first row in each GROUP BY group?