Nth Highest Salary — SQL — InterviewPrepKit

Solving tips

Ranking must be over DISTINCT salary values, so two people paid the same amount share a rank — `DENSE_RANK()` does exactly this, unlike `ROW_NUMBER()` or `RANK()`.
Wrap the lookup in a scalar subquery so that when fewer than N distinct salaries exist the result is a single NULL row rather than an empty result set.
The `LIMIT 1 OFFSET N-1` trick works only after `SELECT DISTINCT ... ORDER BY salary DESC`, and it too returns no row (not NULL) unless wrapped.

Return the third-highest distinct salary. If there are fewer than three distinct salaries, return NULL.

Schema

CREATE TABLE employees (
    id     integer PRIMARY KEY,
    name   text    NOT NULL,
    salary integer NOT NULL
);

Sample data — employees:

id	name	salary
1	Alice	100
2	Bob	90
3	Carol	90
4	Dave	80
5	Eve	70
6	Frank	70

Task

Return a single column named nth_highest_salary containing the third-highest distinct salary (N = 3). Duplicate salaries count once. If fewer than three distinct salaries exist, the query must return one row whose value is NULL.

Expected column: nth_highest_salary.

Expected output

Distinct salaries in descending order are 100, 90, 80, 70; the third is 80.

nth_highest_salary
80

Approach

Rank salaries with DENSE_RANK() so equal salaries share a rank and the rank counts distinct pay levels, then pick the rows with rank = 3. Wrapping that pick in an outer scalar subquery is what satisfies the “return NULL when it doesn’t exist” requirement: a scalar subquery over an empty set yields NULL rather than zero rows.

Query

SELECT (
    SELECT DISTINCT salary
    FROM (
        SELECT salary,
               DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
        FROM employees
    ) ranked
    WHERE rnk = 3
) AS nth_highest_salary;

Walkthrough

The inner window assigns dense ranks over salary DESC: 100 → 1, both 90s → 2, 80 → 3, both 70s → 4. Filtering rnk = 3 leaves the 80 rows; DISTINCT collapses them to the single value 80. The outermost SELECT (...) turns that into one row, nth_highest_salary = 80. If the table held only two distinct salaries, the inner query would return nothing and the scalar subquery would produce a single NULL row, as required.

Complexity & notes

DENSE_RANK is the key choice: ROW_NUMBER would give every 90 a different number and mis-count ranks, while RANK would skip numbers after ties (100→1, 90→2, 90→2, 80→4) so rnk = 3 could match nothing. Two classic alternatives:

-- LIMIT/OFFSET: skip the top N-1 distinct salaries
SELECT (
    SELECT DISTINCT salary FROM employees
    ORDER BY salary DESC
    LIMIT 1 OFFSET 2
) AS nth_highest_salary;

-- correlated count: the salary with exactly 3 distinct salaries >= it
SELECT DISTINCT salary AS nth_highest_salary
FROM employees e
WHERE 3 = (SELECT COUNT(DISTINCT salary)
           FROM employees e2
           WHERE e2.salary >= e.salary);

The LIMIT ... OFFSET form is concise but MySQL/PostgreSQL-specific (SQL Server uses OFFSET ... FETCH, and it too must be wrapped in a scalar subquery to yield NULL). The correlated-count form is fully portable but O(n²). Cost of the window approach is one sort/scan, O(n log n). Watch the off-by-one: the Nth highest uses OFFSET N-1, so N = 3 means OFFSET 2.