Problem Statement
Compare a correlated subquery and a window function to get each department’s highest paid employee.
Explanation
A correlated subquery approach joins employees to a per department maximum and filters on equality. It is portable and simple to read, but can struggle with ties without extra logic.
A window function ranks employees inside each department and then you filter to rank one. This is clear, flexible for ties, and usually efficient because it scans once and avoids self joins.
Code Solution
SolutionRead Only
SELECT dept_id, emp_id, salary
FROM (
SELECT dept_id, emp_id, salary,
ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) rn
FROM employees
) x
WHERE rn=1;Practice Sets
This question appears in the following practice sets:
