Write a query to find the highest-paid employee in each department.
SHORT ANSWER
Compute MAX(salary) per department in a subquery and join back, or use RANK() OVER (PARTITION BY dept ORDER BY salary DESC) and keep rank 1. Both return all tied employees.
Explain it simply
For each department you want the whole employee row, not just the number. A plain GROUP BY gives only the maximum figure. To get the person behind it, either look up the maximum and match it, or rank people inside each department and take the first.
Line by line
Table employees(id, name, dept_id, salary).
sql
-- Option 1, join to the per-department maximum
SELECT e.name, e.dept_id, e.salary
FROM employees e
JOIN (
SELECT dept_id, MAX(salary) AS max_sal
FROM employees
GROUP BY dept_id
) m ON m.dept_id = e.dept_id AND m.max_sal = e.salary;
-- Option 2, window function
SELECT name, dept_id, salary
FROM (
SELECT name, dept_id, salary,
RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk
FROM employees
) t
WHERE rnk = 1;- Option 1: the derived table has one row per department with its top salary; the join keeps employees who earn exactly that.
- Option 2: ranking restarts in each department, rank 1 is the top earner.
- If two people tie, both rows are returned. Use ROW_NUMBER() instead to force exactly one per department.
Complexity
Time: O(n) for the grouped join with hashing, O(n log n) for the window sort. Space: O(n).
What to say in the interview
"I'd use a window function with RANK for clarity, and mention the MAX-join version as the portable alternative. I confirm with the interviewer whether ties should all appear."