Use DENSE_RANK() partitioned by department, ordered by salary descending, and keep rank 2. DENSE_RANK gives tied salaries the same rank and does not skip numbers, so "second highest" means the second distinct salary value.
Query
SELECT department_id, employee_id, salary
FROM (
SELECT department_id, employee_id, salary,
DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk
FROM employees
) t
WHERE rnk = 2;Why not ROW_NUMBER or RANK
Take one department with salaries 90, 90, 80, 70.
salary ROW_NUMBER RANK DENSE_RANK 90 1 1 1 90 2 1 1 80 3 3 2 70 4 4 3
ROW_NUMBER calls the second 90 "second", which is wrong if you mean the second distinct salary. RANK jumps from 1 to 3, so there is no rank 2 at all and the department returns nothing. DENSE_RANK gives 80 as rank 2, and that is the answer the question usually wants. If the interviewer means "second person", say so and use ROW_NUMBER. Ask which meaning they want. It shows you noticed the ambiguity.
A department with only one salary
The query above returns no row for that department, because no rank 2 exists. If the report must list every department with NULL in that case, start from the departments table and left join the result:
SELECT d.department_id, r.salary
FROM departments d
LEFT JOIN (
SELECT department_id, salary FROM (
SELECT department_id, salary,
DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk
FROM employees
) x WHERE rnk = 2
) r ON r.department_id = d.department_id;Note that if two employees share the second salary, the join returns that salary twice. Add SELECT DISTINCT inside if you want one value per department.
Correlated alternative
SELECT e.department_id, MAX(e.salary) FROM employees e WHERE e.salary < (SELECT MAX(salary) FROM employees WHERE department_id = e.department_id) GROUP BY e.department_id;
It works for the 2nd highest only. For Nth, the window version generalises by changing one number.