How can you retrieve the employee(s) with the highest salary in each department using a window function in SQL?

You can achieve this without a CTE by applying the RANK() window function directly in the SELECT clause and filtering in a subquery or WHERE clause. For example:

SELECT employee_id, name, department, salary

FROM (

SELECT employee_id, name, department, salary,

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

FROM employees

) AS ranked

WHERE dept_rank = 1;

The PARTITION BY clause groups rows by department, and ORDER BY salary DESC ensures the highest salaries receive rank 1. Because RANK() assigns the same rank to ties, all top earners per department are returned. The overall time complexity is O(n log n) due to the sort performed by the window function, but it is efficient for typical relational databases.