ROW_NUMBER: Find the Highest-Paid Employee in Each Department

SQL coding challenge · Difficulty: easy · +50 XP

Problem

Each department head wants to see their top earner in the team dashboard. Find the single highest-paid employee in each department.

Tables

Table: employees_wf

| emp_id | emp_name | department | salary |
| 101 | John | IT | 90000 |
| 102 | Alice | IT | 75000 |
| 103 | Bob | IT | 75000 |
| 104 | David | HR | 80000 |
| 105 | Emma | HR | 65000 |

Expected Output

| emp_id | emp_name | department | salary |
| 104 | David | HR | 80000 |
| 101 | John | IT | 90000 |

• Return: emp_id, emp_name, department, salary
• One row per department — the highest paid employee
• Sort by department ascending
• Hint: Use ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) in a subquery, then filter where rn = 1