A CTE with ROW_NUMBER is meant to return the highest-paid employee in each department but returns only one row overall.
Codesql
WITH ranked AS (
SELECT
employee_id,
department_id,
salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM employees
)
SELECT employee_id, department_id, salary
FROM ranked
WHERE rn = 1;
The goal is to return the highest-paid employee in EACH department, but it returns only one row. What is the bug?