SeniorCodeOccasionalNot answered yet

How do you find the highest-paid employee in each department?

Table Employee(id, name, dept_id, salary). Return the full row of the highest-paid employee in each department.

Requirements:

one result row per department, carrying id and name (not just the salary)
rank per department, not globally — a plain GROUP BY + MAX cannot return the name
on a tie at the top, returning exactly one row is acceptable

SELECT id, name, dept_id, salary
FROM   Employee
-- your query here

Write the query.

Number rows per department with ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) in a subquery, then keep WHERE rn = 1. PARTITION BY resets the ranking per department, so row 1 is that department's top earner. A correlated subquery on MAX(salary) per department is the alternative.

✗Using GROUP BY dept_id + MAX(salary) and expecting the employee name to come along
✗Ranking globally without PARTITION BY dept_id
✗Using LIMIT 1 and getting only one department's top earner

→How does PARTITION BY differ from GROUP BY in what it returns?
→When would you prefer RANK over ROW_NUMBER for ties at the top of a department?

Contents

Task

Find the highest-paid employee in each department.

-- Window-function approach
SELECT id, name, dept_id, salary
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
    FROM   Employee
) t
WHERE rn = 1;

-- Correlated-subquery approach
SELECT *
FROM   Employee e
WHERE  salary = (SELECT MAX(salary) FROM Employee x WHERE x.dept_id = e.dept_id);

How it works

This is the "top-N per group" pattern. ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) numbers rows within each department: PARTITION BY restarts the count for each dept_id, and ORDER BY salary DESC puts the largest salary first. The outer WHERE rn = 1 keeps each department's leader — with all its columns (id, name).

The advantage over GROUP BY dept_id + MAX(salary): grouping returns the maximum but cannot tell you who earns it (you cannot select the name without aggregating it). The window function keeps the whole row.

⚠️ On ties at the top, ROW_NUMBER keeps exactly one; use RANK if you want everyone at the maximum.