What is the logic to select the top two highest paid employees in each department?

To retrieve the top two highest paid employees per department, you can use a window function such as ROW_NUMBER() partitioned by department and ordered by salary in descending order. For example:

SELECT empid, ename, dept_id, salary

FROM (

SELECT e.*, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn

FROM employee e

) t

WHERE rn <= 2;

This assigns a rank to each employee within their department based on salary. The outer query filters to the first two ranks. The approach runs in O(n log n) time due to sorting, but most databases optimize the window function. Alternatively, you could use a correlated subquery with LIMIT 2 per group, but the window function is clearer and more efficient in modern SQL engines.