How do you get the top N rows per group?
SQLLevel: IntermediateWindow Functions
How do you get the top N rows per group?
Hints
- Define one output row and the source-table keys.
- Check duplicates, NULLs and the order of filtering and aggregation.
Answer
Use ROW_NUMBER() with PARTITION BY and then filter in a CTE or subquery.
WITH ranked AS (
SELECT
name,
department,
salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
FROM employees
)
SELECT name, department, salary
FROM ranked
WHERE rn <= 3; -- Top 3 per department
You can't put window functions in WHERE directly (they run after WHERE), so you need a subquery or CTE wrapper.
Source and licence: SQL Interview Questions — Arda Kocadoru · MIT