Top-N Rows Per Group With ROW_NUMBER
The canonical partition-and-rank pattern for 'top 3 per category'.
The Top-N Per Group Question
One of the most common SQL interview prompts sounds simple: "Return the top 3 highest-paid employees in each department." Candidates who reach for LIMIT immediately fail, because LIMIT caps the whole result set, not each group.
The interviewer is checking whether you know window functions. The canonical answer is: number the rows inside each group, then keep the rows whose number is ≤ N. This lesson builds that pattern step by step.
Why LIMIT Cannot Solve It
Suppose you write the query below. It returns only 3 rows total across the entire table, not 3 per department.
LIMIT (or TOP, or FETCH FIRST) operates on the final result set. There is no per-group LIMIT in standard SQL. When an interviewer hears you suggest LIMIT 3 for a per-group problem, it signals you have not internalized partitioning.
-- WRONG: only 3 rows total, not 3 per department
SELECT department, name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;All lessons in this course
- Top-N Rows Per Group With ROW_NUMBER
- Handling Ties in Top-N
- Deduplicating Rows Safely
- Keeping the Latest Row Per Key