Lesson 5 of 14 · 24 min
Top N per group
By the end of this lesson you can write top-N-per-group queries with a ranking function, a CTE and an outer filter, decide how ties should be treated, and explain why LIMIT alone cannot do it.
ORDER BY … LIMIT 3 gives the top 3 rows of the whole result. Ask for the top 3 products in each category and LIMIT is stuck: it can't restart for every group. The standard solution has three steps:
- Aggregate to the grain you want to rank (one row per product, with its units sold).
- Rank inside each group with a window function:
ROW_NUMBER() OVER (PARTITION BY category ORDER BY units_sold DESC, tie_breaker). - Filter on the rank in an outer query:
WHERE rn <= 3.
Step 3 must be outside, because a window function can't appear in WHERE: WHERE runs before the ranks exist. A CTE keeps the three steps readable.
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons