The CEO wants the top 2 highest-paid employees in every department, for a compensation benchmark, not just the single highest (that was ROW_NUMBER()'s own lesson) and not just one summary number per department. This exact pattern, "top N per group", shows up constantly in real reporting, and in interviews.
Learning Objectives
- Explain precisely why a plain GROUP BY can't answer a top-N-per-group question.
- Combine ROW_NUMBER(), PARTITION BY, and a subquery to solve it correctly.
- Recognize this pattern as one of the most common real-world and interview uses of window functions.
Each Department
Its own group
ROW_NUMBER() by Salary
Ranked within each group
Filtered in an Outer Query
WHERE salary_rank <= 2
Top N Rows, Per Group
Full row detail kept, not collapsed
Why GROUP BY Alone Can't Do This
SELECT department_id, MAX(salary) FROM employees GROUP BY department_id; finds the single highest salary per department, but loses which employee that was, and there's no way to ask GROUP BY for "the top 2" at all, it only ever collapses each group down to one row. Top-N-per-group genuinely needs a per-row rank, which is exactly what a window function provides.
The Top 2 Earners in Every Department
The inner query ranks every employee within their own department. The outer WHERE, running after that ranking is already computed, keeps only the top 2 from each department, full row detail intact, nothing collapsed.
SELECT * FROM (
SELECT first_name, last_name, department_id, salary,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM employees
) ranked
WHERE salary_rank <= 2;Try It Yourself
Write a query returning the single most recently hired employee in each department, on MariaDB.
SELECT * FROM (
SELECT first_name, last_name, department_id, hire_date,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY hire_date DESC) AS recency_rank
FROM employees
) ranked
WHERE recency_rank = 1;Frequently Asked Questions
Should RANK() ever be used instead of ROW_NUMBER() for a top-N-per-group query?
Yes, when ties should both be included rather than arbitrarily picking one, WHERE salary_rank <= 2 using RANK() instead of ROW_NUMBER() would include 3 employees if the 2nd and 3rd highest happen to tie, since RANK() gives them the same rank. Which one is correct depends entirely on whether a tie should count as both being "in the top 2" or not.
Is a CTE better than a subquery for this pattern?
Functionally identical, a CTE (WITH ranked AS (...) SELECT * FROM ranked WHERE salary_rank <= 2;, covered in the next module) is usually considered more readable once the inner query grows more complex, but a plain subquery works exactly the same way and needs no new syntax.
Common Beginner Mistakes
Trying WHERE ROW_NUMBER() OVER (...) <= 2 directly, without a subquery
This is rejected on every database in this course, for the same reason covered in the ROW_NUMBER() lesson, WHERE is evaluated before window functions, so the rank doesn't exist yet at the point WHERE needs it. The ranking has to be computed in an inner query first, then filtered in an outer one.
Using LIMIT/TOP instead of ROW_NUMBER() and expecting it to work per group
A plain LIMIT 2 or TOP 2 applies to the entire result set, not per group, it returns only 2 rows total, not 2 rows from every department. LIMIT/TOP has no concept of "per group" on its own, that's exactly the gap ROW_NUMBER() with PARTITION BY fills.
Best Practices
- Reach for ROW_NUMBER() plus a subquery (or CTE) as the default solution whenever a question involves "top N" or "bottom N" within each group.
- Choose RANK() over ROW_NUMBER() specifically when a tie at the cutoff should include every tied row, not just one.
- Add an explicit tiebreaker column to ORDER BY when which specific row wins a tie genuinely matters.
Interview Questions
Write a query to find the top 3 highest-paid employees in each department.
SELECT * FROM (SELECT first_name, last_name, department_id, salary, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank FROM employees) ranked WHERE salary_rank <= 3; — the inner query ranks every employee within their own department by salary, the outer WHERE keeps only the top 3 from each one.
Why can't this problem be solved with GROUP BY and a HAVING clause instead?
GROUP BY collapses each department down to a single row, there's no way to keep 3 individual employee rows per department using GROUP BY and HAVING alone, both operate on already-collapsed groups, not on a per-row rank. A window function is required specifically because it can assign a rank to every individual row while still keeping all of them in the result.
Summary
Top-N-per-group needs a per-row rank, not a collapsed GROUP BY result, that's precisely what ROW_NUMBER() (or RANK(), when ties should all be included) with PARTITION BY provides. Filtering on that rank always requires an outer query, a subquery or CTE, since WHERE can't see a window function's result in the same SELECT it was computed in.
That completes the Window Functions module: OVER/PARTITION BY, the three ranking functions and exactly how they differ, NTILE()'s bucketing, LAG()/LEAD()'s neighboring-row lookups, FIRST_VALUE()/LAST_VALUE()'s frame-dependent behavior, running totals and moving averages, and this capstone pattern tying ROW_NUMBER() together with a subquery.
What's Next?
Common Table Expressions are next: a cleaner way to write the subquery this very lesson just relied on, among many other uses.
Ready to Practice SQL?
Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.
Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.
Or try 50 SQL practice questions →