“The latest order of each customer.” “The three best-selling products of each category.” “The highest-paid employee in each department.” This is the top N per group problem, and it is one of the most common SQL questions.
CREATE TABLE products (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20), category VARCHAR(12), units_sold INT);
INSERT INTO products (name, category, units_sold) VALUES
('Notebook', 'stationery', 520), ('Gel Pen', 'stationery', 980), ('Marker', 'stationery', 310), ('Stapler', 'stationery', 310), ('Folder', 'stationery', 150),
('Backpack', 'bags', 240), ('Laptop Sleeve', 'bags', 410), ('Duffel', 'bags', 90),
('Desk Lamp', 'home', 175), ('Bottle', 'home', 640);The window function solution
- Number the rows in each group in the order you care about.
- Keep the rows whose number is at most N.
WITH ranked AS (
SELECT name, category, units_sold,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY units_sold DESC, id) AS rn
FROM products
)
SELECT category, rn AS position, name, units_sold
FROM ranked
WHERE rn <= 2
ORDER BY category, rn;+------------+----------+---------------+------------+
| category | position | name | units_sold |
+------------+----------+---------------+------------+
| bags | 1 | Laptop Sleeve | 410 |
| bags | 2 | Backpack | 240 |
| home | 1 | Bottle | 640 |
| home | 2 | Desk Lamp | 175 |
| stationery | 1 | Gel Pen | 980 |
| stationery | 2 | Notebook | 520 |
+------------+----------+---------------+------------+
6 rows in setThe two best sellers of each category. Change rn <= 2 to any N. The PARTITION BY is the “per group”, the ORDER BY is the “top by what”.
Exactly one row per group: N = 1
WITH ranked AS (
SELECT name, category, units_sold, ROW_NUMBER() OVER (PARTITION BY category ORDER BY units_sold DESC, id) AS rn FROM products
)
SELECT category, name AS best_seller, units_sold FROM ranked WHERE rn = 1 ORDER BY category;+------------+---------------+------------+
| category | best_seller | units_sold |
+------------+---------------+------------+
| bags | Laptop Sleeve | 410 |
| home | Bottle | 640 |
| stationery | Gel Pen | 980 |
+------------+---------------+------------+
3 rows in setTies: ROW_NUMBER vs RANK vs DENSE_RANK
Marker and Stapler both sold 310. With ROW_NUMBER, one of them is arbitrarily in the top 3 of stationery and the other is cut. If ties should be kept together, use RANK():
WITH ranked AS (
SELECT name, category, units_sold,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY units_sold DESC) AS row_no,
RANK() OVER (PARTITION BY category ORDER BY units_sold DESC) AS rank_no
FROM products
WHERE category = 'stationery'
)
SELECT name, units_sold, row_no, rank_no FROM ranked ORDER BY row_no;+----------+------------+--------+---------+
| name | units_sold | row_no | rank_no |
+----------+------------+--------+---------+
| Gel Pen | 980 | 1 | 1 |
| Notebook | 520 | 2 | 2 |
| Marker | 310 | 3 | 3 |
| Stapler | 310 | 4 | 3 |
| Folder | 150 | 5 | 5 |
+----------+------------+--------+---------+
5 rows in setThe two rows tie at rank 3. If you keep rank_no <= 3, both are returned (four rows, not three). If you keep row_no <= 3, only one is, and which one is not defined without a tie-breaker. Decide which behaviour the question needs.
| You want | Use |
|---|---|
| Exactly N rows per group | ROW_NUMBER() with a unique tie-breaker |
| N rows, ties included | RANK() |
| The N highest distinct values, ties included | DENSE_RANK() |
The older way: a correlated subquery
Before window functions, “top 1 per group” used MAX in a subquery:
SELECT p.category, p.name, p.units_sold
FROM products p
WHERE p.units_sold = (SELECT MAX(x.units_sold) FROM products x WHERE x.category = p.category)
ORDER BY p.category;+------------+---------------+------------+
| category | name | units_sold |
+------------+---------------+------------+
| bags | Laptop Sleeve | 410 |
| home | Bottle | 640 |
| stationery | Gel Pen | 980 |
+------------+---------------+------------+
3 rows in setIt works for N = 1 and keeps ties. It does not extend to N = 3 without nasty counting subqueries. The window function version is easier and usually faster.
The LATERAL way
With a small list of parents and an index on the child table, a LATERAL join can be the fastest (LATERAL):
CREATE TABLE categories (name VARCHAR(12) PRIMARY KEY);
INSERT INTO categories VALUES ('stationery'), ('bags'), ('home');
CREATE INDEX idx_category_sold ON products (category, units_sold);
SELECT c.name AS category, top.name AS product, top.units_sold
FROM categories c
JOIN LATERAL (SELECT p.name, p.units_sold FROM products p WHERE p.category = c.name ORDER BY p.units_sold DESC LIMIT 2) AS top ON TRUE
ORDER BY c.name, top.units_sold DESC;+------------+---------------+------------+
| category | product | units_sold |
+------------+---------------+------------+
| bags | Laptop Sleeve | 410 |
| bags | Backpack | 240 |
| home | Bottle | 640 |
| home | Desk Lamp | 175 |
| stationery | Gel Pen | 980 |
| stationery | Notebook | 520 |
+------------+---------------+------------+
6 rows in setTop N overall and per group at once
Overall best sellers, with their rank inside the category:
SELECT name, category, units_sold, category_rank
FROM (
SELECT name, category, units_sold, RANK() OVER (PARTITION BY category ORDER BY units_sold DESC) AS category_rank
FROM products
) t
WHERE category_rank = 1
ORDER BY units_sold DESC;+---------------+------------+------------+---------------+
| name | category | units_sold | category_rank |
+---------------+------------+------------+---------------+
| Gel Pen | stationery | 980 | 1 |
| Bottle | home | 640 | 1 |
| Laptop Sleeve | bags | 410 | 1 |
+---------------+------------+------------+---------------+
3 rows in setPerformance
The window function must read and sort all the rows (per partition) before it can filter. For a huge table and a small N, the LATERAL approach with an index on (group_column, sort_column) reads only N rows per group. For moderate tables, the window function is fine and clearer.
Always add a tie-breaker
ORDER BY units_sold DESC, id makes the result stable. Without it, repeated runs can pick different rows among ties.