Top-N per Group

Part of the Beyond the Basics section of Coddy's SQL journey — lesson 23 of 27.

"Highest-paid employee per department" is the textbook example. The recipe is always ROW_NUMBER() with a PARTITION BY, then filter to row 1.

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 = 1

Want top 3 instead of top 1? Change WHERE rn = 1 to WHERE rn <= 3. Want to allow ties at the top? Swap ROW_NUMBER() for RANK().

Challenge

Easy

Available tables and columns:
employees: id, name, department, salary

Return the highest-paid employee in each department. Output name, department, and salary, ordered by department. If two employees tie for the top within a department, return only one: the one with the lower id.

Cheat sheet

To get the top N rows per group, use ROW_NUMBER() with PARTITION BY, then filter:

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 = 1

Change WHERE rn = 1 to WHERE rn <= 3 for top 3
Swap ROW_NUMBER() for RANK() to allow ties

Try it yourself

WITH ranked AS (
    -- ROW_NUMBER() OVER (PARTITION BY department ORDER BY ...)
)
SELECT name, department, salary FROM ranked WHERE rn = 1
ORDER BY department