Second Highest Salary SQL Query | SQL Interview Question

Problem statement

Prompt

Given the employee table below, write a query that returns the second-highest distinct salary from the salary column.

"Distinct" means identical salaries count once. If three employees earn 100, 100, and 90, the salaries are 100 and 90, so the answer is 90.
If there is no second-highest salary (fewer than two distinct salaries, or an empty table), the query must return one row with a `NULL` value — not zero rows.
NULL salaries must be ignored: they are not a value and should never be returned as the answer.

The result should have a single column (name it second_highest_salary) and a single row.

Example

id	salary
1	100
2	100
3	90
4	80

Expected result:

second_highest_salary
90

Constraints

employee.id is the primary key and is never NULL.
employee.salary may be NULL.

Tips

Think about what happens with duplicates before you write LIMIT 1 OFFSET 1 — an unordered DISTINCT + OFFSET approach is fragile.
Aggregates like MAX over a set of values are a clean way to guarantee "one row, or NULL when empty".

Schema

CREATE TABLE employee (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    salary INTEGER  -- NULL allowed
);

Sample data

INSERT INTO employee (id, name, salary) VALUES
  (1, 'Alice',   100),
  (2, 'Bob',     100),
  (3, 'Carol',   90),
  (4, 'Dave',    80),
  (5, 'Eve',     75);

Additional hidden fixtures are applied during validation to test edge cases.

Solution

One correct approach — try solving it yourself in the practice editor first, then compare. There is usually more than one valid solution.

-- Reference: second-highest distinct salary, ignoring NULLs, as a single row.
WITH distinct_salaries AS (
    SELECT DISTINCT salary
    FROM employee
    WHERE salary IS NOT NULL
)
SELECT MAX(salary) AS second_highest_salary
FROM distinct_salaries
WHERE salary < (SELECT MAX(salary) FROM distinct_salaries);

Key concepts

SELECT
DISTINCT
Subquery in FROM
Aggregate MAX
NULL handling

Second Highest Salary