Second-Highest Salary (SQL)

Problem

Given:

sql

CREATE TABLE employees (
    id     INT PRIMARY KEY,
    name   VARCHAR(100),
    salary DECIMAL(10,2)
);

Return the second-highest salary. If there is no second salary (e.g. only one row, or all salaries identical), return NULL.

Approach 1 — Subquery with MAX

sql

SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

Clean, portable, returns NULL when no such row exists.

Approach 2 — DISTINCT + ORDER BY + LIMIT

sql

SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

Pitfall: if there's no second salary, this returns no row instead of NULL. To force NULL:

sql

SELECT (
  SELECT DISTINCT salary FROM employees
  ORDER BY salary DESC LIMIT 1 OFFSET 1
) AS second_highest;

Approach 3 — Window function (best for top-N variants)

sql

SELECT salary AS second_highest
FROM (
  SELECT salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS rk
  FROM employees
) t
WHERE rk = 2;

For the third-highest, just change rk = 2 to rk = 3 — no rewrite.

[object Object] vs [object Object] vs [object Object]

Salaries: 90, 80, 80, 70.

function	output
`ROW_NUMBER`	1, 2, 3, 4
`RANK`	1, 2, 2, 4 (skips 3)
`DENSE_RANK`	1, 2, 2, 3

For "the salary at rank 2", DENSE_RANK is what you usually want.

What interviewers look for

Awareness of the NULL when no row edge case.
Knowing DENSE_RANK vs RANK vs ROW_NUMBER.
Asking clarifying questions: "ties count once or separately?", "should NULL salaries be excluded?".
Reading the query plan with EXPLAIN — the window-function approach scans once; the subquery-with-MAX scans twice.

Variation — N-th highest as a function

sql

CREATE FUNCTION nth_highest_salary(N INT) RETURNS DECIMAL(10,2)
BEGIN
    DECLARE result DECIMAL(10,2);
    SET N = N - 1;
    SELECT DISTINCT salary INTO result
    FROM employees
    ORDER BY salary DESC LIMIT 1 OFFSET N;
    RETURN result;
END;

Interview Questions →Developer Roadmaps →