In SQL, how do you return the three highest-paid employees in each department in one query?

## The problem shape "Top N per group" (also called greatest-N-per-group) means: for every value of some grouping column, return the N rows with the best value of some metric — the three highest-paid employees in each department, the five most recent orders per customer, the top two products per category. The output keeps whole rows, one per qualifying record, so plain aggregation is the wrong tool: `GROUP BY department_id` collapses each department to a single row, which can give you `MAX(salary)` but not the three employees behind it. ## Why the filter needs a second query level A window function is evaluated late in the logical processing of a query — after `FROM`, `WHERE`, `GROUP BY` and `HAVING` have produced the row set it operates on. That ordering is exactly why `WHERE ROW_NUMBER() OVER (...) <= 3` is rejected by every conforming engine: at the time `WHERE` runs, the rank does not exist yet. `HAVING` does not rescue it either, because `HAVING` also runs before window functions. The fix is structural. Compute the rank in an inner query — a CTE (`WITH ranked AS (...)`) or a derived table (`FROM (...) AS t`) — where it becomes an ordinary output column. In the outer query it is just a column, and an ordinary `WHERE` can filter it. ```sql WITH ranked AS ( SELECT employee_id, name, department_id, salary, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn FROM employees ) SELECT department_id, name, salary FROM ranked WHERE rn <= 3 ORDER BY department_id, rn; ``` ## Reading the OVER clause `PARTITION BY department_id` splits the rows into independent groups; the numbering restarts at 1 in each one. Unlike `GROUP BY`, partitioning does not collapse rows — every input row still comes out, now carrying its rank. `ORDER BY salary DESC` defines "best first" inside each partition. Change `DESC` to `ASC` and the same query returns the three lowest-paid per department, which is the single most common typo in this pattern. If you want the top three overall rather than per department, drop `PARTITION BY` and keep `ORDER BY salary DESC` — though for a whole-table top N, `ORDER BY salary DESC FETCH FIRST 3 ROWS ONLY` is simpler and clearer. ## Choosing the ranking function `ROW_NUMBER()` assigns 1, 2, 3, … with no duplicates, so `rn <= N` returns at most N rows per group. `RANK()` gives tied rows the same number and then skips, so `rnk <= 3` can return more than three rows when the third place is tied. `DENSE_RANK()` numbers distinct metric values, so `<= 3` means "the top three salary levels", however many people sit on them. Pick the one that matches the requirement: exactly N rows, N places including ties, or N distinct values. ## Determinism With `ROW_NUMBER()`, if two employees in a department earn the same salary, one of them gets rank 2 and the other rank 3, and nothing in the query says which. Re-running the query may return them in the other order. When the choice matters — especially for `rn = 1` style queries — add a unique tiebreaker to the window `ORDER BY`, for example `ORDER BY salary DESC, employee_id`. ## Common mistakes - `SELECT department_id, name, MAX(salary) FROM employees GROUP BY department_id` — this is not even a legal query in a strict engine, because `name` is neither grouped nor aggregated, and it could never return three rows per department anyway. - `ORDER BY department_id, salary DESC LIMIT 3` — returns three rows in total, not three per department. - Putting `LIMIT 3` in the inner query — it truncates the ranked set globally before the outer filter ever sees the other departments. - Forgetting the window `ORDER BY` entirely: `ROW_NUMBER() OVER (PARTITION BY department_id)` numbers rows in an unspecified order, so "top 3" becomes "any 3". ## Edge cases worth stating out loud A department with two employees contributes two rows — nothing pads the group up to N. A department with no employees does not appear at all, because the pattern operates on `employees`; if you need every department listed, you must start from the department table and outer-join. And if `salary` is nullable, remember that engines differ on where NULLs sort by default, so a NULL salary can land at rank 1 under `DESC` unless you spell out `NULLS LAST`.