Write a SQL query to fetch the top two highest paid employees in each department.

A concise solution uses the ROW_NUMBER() window function:

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;

The inner query assigns a rank to each employee within their department based on salary. The outer query filters to the top two ranks. This runs in O(n log n) time due to the sort, but the database engine optimizes the window. If you prefer a subquery approach, you could write:

SELECT e.empid, e.ename, e.dept_id, e.salary

FROM employee e

WHERE e.salary >= (

SELECT MIN(salary)

FROM (

SELECT salary
FROM employee e2
WHERE e2.dept_id = e.dept_id
ORDER BY salary DESC
LIMIT 2

) sub

);

However, the window function is clearer and generally faster.