How would you find the employee(s) with the highest salary in each department using SQL?

A common approach is to use a Common Table Expression (CTE) combined with the RANK() window function. First, partition the rows by department and order by salary descending. RANK() assigns 1 to the highest salary within each partition, and ties receive the same rank. The CTE looks like:

WITH Ranked AS (

SELECT employee_id, name, department, salary,

     RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank

FROM employees

)

SELECT employee_id, name, department, salary

FROM Ranked

WHERE dept_rank = 1;

This returns every employee who shares the top salary in their department. Complexity is O(n log n) due to the sorting required for the window function, but it runs in a single scan of the table. Using RANK() instead of ROW_NUMBER() ensures ties are preserved, which is often required by business rules.