Top N per group
Marketing wants a “shop by category” carousel on the homepage: “Top 3 products, but per category — every category needs to show something.”
Top N by revenue, we just did this. Join to orders, sum, order by revenue, limit 3.
Try it. Then check which categories show up.
SELECT p.name, p.category, SUM(o.amount) AS revenue
FROM products p
JOIN orders o ON o.product_id = p.id
WHERE o.status = 'completed'
GROUP BY p.id, p.name, p.category
ORDER BY revenue DESC
LIMIT 3;| name | category | revenue |
|---|---|---|
| Pulse Smartwatch | Wearables | 399.98 |
| Aurora Earbuds | Audio | 239.97 |
| Nova Keyboard | Computing | 178 |
Marketing: “Where’s Accessories? Where’s Home? We said every category.”
LIMIT 3 did exactly what it was told: take the top 3 rows, full stop. It has no idea categories exist — it just sorted every product by revenue and sliced off the top of the whole list. Two categories happened to own all three spots, so the rest got nothing.
What you actually want is a limit that resets for every group. SQL doesn’t have a “LIMIT per category” keyword — but you’ve already met the tool that numbers rows within a group and starts over at the next one: PARTITION BY, from window functions.
Rank within each group, restarting the count at every group.
First, number every product within its own category, ordered by revenue — the same ROW_NUMBER() from the ranking lesson, just partitioned this time:
SELECT
p.name,
p.category,
SUM(o.amount) AS revenue,
ROW_NUMBER() OVER (
PARTITION BY p.category
ORDER BY SUM(o.amount) DESC
) AS rn
FROM products p
JOIN orders o ON o.product_id = p.id
WHERE o.status = 'completed'
GROUP BY p.id, p.name, p.category;rn restarts at 1 for every new category — Accessories gets its own #1, #2, #3, completely independent of Wearables’ #1, #2, #3. Now filter, the same way you’d filter anything else that came from a window function: wrap it and check the outer query.
WITH ranked AS (
SELECT
p.name,
p.category,
SUM(o.amount) AS revenue,
ROW_NUMBER() OVER (
PARTITION BY p.category
ORDER BY SUM(o.amount) DESC
) AS rn
FROM products p
JOIN orders o ON o.product_id = p.id
WHERE o.status = 'completed'
GROUP BY p.id, p.name, p.category
)
SELECT name, category, revenue
FROM ranked
WHERE rn <= 3
ORDER BY category, rn;| name | category | revenue |
|---|---|---|
| Comet Charger 65W | Accessories | 74.97 |
| Nova Backpack | Accessories | 64 |
| Comet Charger 20W | Accessories | 44.97 |
| Aurora Earbuds | Audio | 239.97 |
| Nova Speaker Mini | Audio | 99.98 |
| Nova Keyboard | Computing | 178 |
| Orbit Webcam | Computing | 59.99 |
| Nova Mouse | Computing | 39 |
| Halo Desk Lamp | Home | 69 |
| Pulse Smartwatch | Wearables | 399.98 |
| Pulse Band | Wearables | 29.99 |
Every category is represented now. Audio, Home, and Wearables only have one or two products with completed orders at all, so “top 3” just returns everything they’ve got — which is correct. A per-group limit that can’t be satisfied isn’t a bug, it’s just an honest answer.
Running…
Try it: What if marketing only has room for the top 2 per category, not 3 — change rn <= 3 to rn <= 2?
Run your query to see results here.
Now it actually trims something: the Nova Mouse drops out of Computing and the Comet Charger 20W drops out of Accessories, because those two categories genuinely had 3+ products to choose from. Audio, Home, and Wearables are untouched — they never had more than 2 to begin with. This is the difference between the two examples: N=3 only exposed the “missing categories” bug, while N=2 is small enough to also show the pattern actually cutting rows.
HR wants the top 2 highest-paid employees in every department, for a compensation review. Return name, department, and salary. Two employees can tie on salary — when that happens, break the tie by whoever was hired first.
Hint 1
Same shape as the products-per-category problem: rank rows within a group, then keep the ones at or above the cutoff rank.
Hint 2
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC, hire_date ASC) gives every employee a rank inside their own department, with ties broken by seniority.
Hint 3
Wrap that in a CTE (or subquery), then in the outer query: WHERE rn <= 2.
Solution
Running…
Engineering has five employees and two salary ties in it — the exact situation where a plain LIMIT would silently pick whichever tied row the database feels like returning first. Breaking the tie explicitly with hire_date ASC makes the ranking deterministic instead of accidental. Different table, same pattern as the product carousel: partition, rank, cut.
A single LIMIT only ever cuts the whole result once. To cap rows per group, rank rows with a window function partitioned by that group (ROW_NUMBER, RANK, or DENSE_RANK), then filter on the rank in an outer query. Always give ties an explicit tiebreaker, or the “top N” you get back depends on luck.