Schema and sample data
| emp_id | name | department | salary |
|---|---|---|---|
| 1 | Ava Torres | Engineering | 72000.00 |
| 2 | Ben Ncube | Sales | 48000.00 |
| 3 | Cara Singh | Engineering | 95000.00 |
| 4 | Deng Liu | Support | 50000.00 |
| 5 | Emma Rossi | Sales | 61000.00 |
| 6 | Farah Khan | Support | 67000.00 |
| 7 | Gil Owens | Engineering | 88000.00 |
Show setup SQL (copy to run)
CREATE TABLE Employees (
emp_id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
department VARCHAR(30),
salary DECIMAL(10,2) NOT NULL
);
INSERT INTO Employees (emp_id, name, department, salary) VALUES
(1, 'Ava Torres', 'Engineering', 72000.00),
(2, 'Ben Ncube', 'Sales', 48000.00),
(3, 'Cara Singh', 'Engineering', 95000.00),
(4, 'Deng Liu', 'Support', 50000.00),
(5, 'Emma Rossi', 'Sales', 61000.00),
(6, 'Farah Khan', 'Support', 67000.00),
(7, 'Gil Owens', 'Engineering', 88000.00);From the Employees table, return the highest paid employee in each department. For every department output three columns: department, name and salary. There is exactly one clear top earner per department in the sample data. Sort the result by department in ascending (A to Z) order.
Expected result
| department | name | salary |
|---|---|---|
| Engineering | Cara Singh | 95000.00 |
| Sales | Emma Rossi | 61000.00 |
| Support | Farah Khan | 67000.00 |
Show hint
Number the rows inside each department. Use ROW_NUMBER() with PARTITION BY department ORDER BY salary DESC so the top earner in each group gets number 1, then keep only those rows. Because a window function cannot go in a WHERE clause, compute it in a CTE (or subquery) first and filter the result.
Try to write the query yourself before you open this.
Show solution and explanation
WITH ranked AS (
SELECT
department,
name,
salary,
ROW_NUMBER() OVER (
PARTITION BY department
ORDER BY salary DESC
) AS rn
FROM Employees
)
SELECT department, name, salary
FROM ranked
WHERE rn = 1
ORDER BY department;The core idea is the top-N-per-group pattern. Inside the CTE, PARTITION BY department restarts the numbering for each department, and ORDER BY salary DESC puts the biggest salary first, so ROW_NUMBER() assigns rn = 1 to the highest paid person in each group. In Engineering that is Cara Singh (95000) ahead of Gil Owens (88000) and Ava Torres (72000); in Sales it is Emma Rossi (61000) ahead of Ben Ncube (48000); in Support it is Farah Khan (67000) ahead of Deng Liu (50000).
Why the CTE at all? A window function is evaluated after WHERE, so you cannot write WHERE ROW_NUMBER() OVER (...) = 1 directly - SQL Server rejects it. Computing rn in the ranked CTE and filtering WHERE rn = 1 in the outer query sidesteps that ordering. The final ORDER BY department gives the stable A to Z output the question asks for.
Watch out for ties. ROW_NUMBER() always returns exactly one row per department even when two people share the top salary - it breaks the tie arbitrarily, so you may drop a legitimate co-top-earner. If you want every tied top earner, swap in RANK() with the same window and filter WHERE rnk = 1: RANK gives both tied rows the value 1, so both survive. Choose ROW_NUMBER() when you need exactly one winner and RANK() when ties should all count. This is the classic top-N per group scenario.