How do you find the second-highest salary in each department?

Instruction: Return the second distinct non-NULL salary per department, explaining ties and departments with fewer salaries.

Context: Use DENSE_RANK to find each department’s second-highest distinct salary and handle tied salaries correctly.

Updated

Example Answer

I would first clarify whether “second-highest” means the second distinct salary or the second employee. For the second distinct salary, I use DENSE_RANK within each department and keep rank two. Tied salaries receive the same rank without leaving gaps. I exclude NULL salaries before ranking.

Worked example

This query runs in PostgreSQL and SQLite versions with window functions:

WITH employees(department_id, salary) AS (
    VALUES (1, 100), (1, 100), (1, 80), (1, 80),
           (2, 90), (2, NULL), (3, 70), (3, 60)
), ranked AS (
    SELECT department_id, salary,
           DENSE_RANK() OVER (
               PARTITION BY department_id ORDER BY salary DESC
           ) AS salary_rank
    FROM employees
    WHERE salary IS NOT NULL
)
SELECT DISTINCT department_id, salary
FROM ranked
WHERE salary_rank = 2
ORDER BY department_id;

The result is (1, 80) and (3, 60). Department 2 has no second distinct salary, so it is omitted. To return employees earning that salary, include their IDs and omit DISTINCT. For the second employee instead, use ROW_NUMBER with a deterministic tie-breaker.

Reference

PostgreSQL: window functions

Related Questions