Lesson 12.4 · Reading time: ~7 min

"Find the second highest salary" is one of the most frequent SQL interview questions, usually followed by "now the Nth highest" and "now in each department". In this lesson you'll solve it five ways: MAX with a subquery, LIMIT ... OFFSET, DENSE_RANK(), and a correlated subquery, and you'll see the traps interviewers look for: duplicate salaries, a missing Nth value, and groups.

Nth Highest Salary in SQL

The task sounds trivial, which is exactly why it is popular: a first attempt usually works on the sample data and fails on an edge case. A good answer handles three things: ties (two employees with the same salary), no answer (fewer than N different salaries), and groups (the Nth salary in each department).

The examples use MySQL 8.0 syntax and create their own table, so run them in the playground on a database without sample data.

Sample Data

CREATE TABLE employee (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    department VARCHAR(30) NOT NULL,
    salary INT NOT NULL
);

INSERT INTO employee VALUES
    (1, 'Alice', 'Sales', 5000),
    (2, 'Bob', 'Sales', 7000),
    (3, 'Carol', 'Sales', 7000),
    (4, 'Dave', 'IT', 9000),
    (5, 'Eve', 'IT', 6500),
    (6, 'Frank', 'IT', 9000),
    (7, 'Grace', 'HR', 4800),
    (8, 'Heidi', 'HR', 5200);

The distinct salaries from the top are 9000, 7000, 6500, 5200, 5000, 4800. Two people share 9000 and two share 7000, so the second highest salary is 7000, not 9000.

Solution 1: MAX Below the MAX

The second highest salary is the largest salary that is smaller than the largest one:

SELECT MAX(salary) AS second_highest_salary
FROM employee
WHERE salary < (SELECT MAX(salary) FROM employee);

Result: 7000.

This is short, works in every database, handles ties and returns NULL when there is no second salary, because MAX of an empty set is NULL. Its limit: it only works for N = 2. For the third salary you would need another nested level.

Solution 2: DISTINCT with LIMIT and OFFSET

Sort the salaries from the top and skip N − 1 of them:

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

Result: 7000.

DISTINCT is essential. Without it, the sorted list is 9000, 9000, 7000, …, and OFFSET 1 lands on the second 9000:

-- Wrong: returns the top salary again because of the tie
SELECT salary
FROM employee
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

Result: 9000, the highest salary, not the second highest.

Returning NULL When There Is No Answer

If N is larger than the number of distinct salaries, the query returns no rows. Interview tasks often require a NULL instead. Wrap the query in a scalar subquery: a subquery that finds nothing returns NULL.

SELECT (
    SELECT DISTINCT salary
    FROM employee
    ORDER BY salary DESC
    LIMIT 1 OFFSET 9
) AS tenth_highest_salary;

Result: one row with NULL; there are only 6 distinct salaries.

LIMIT ... OFFSET is MySQL, PostgreSQL and SQLite syntax. SQL Server and Oracle 12c+ use OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY.

Solution 3: DENSE_RANK

A window function ranks every row, and the outer query picks the rank you need. This is the most flexible solution and the one interviewers usually expect:

WITH ranked AS (
    SELECT name, salary,
           DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
    FROM employee
)
SELECT name, salary
FROM ranked
WHERE salary_rank = 2;

Result: Bob and Carol, both with 7000.

It also answers the follow-up question "who earns the second highest salary". Change 2 to any N. Only DENSE_RANK is right here:

ROW_NUMBER gives equal salaries different numbers, and RANK leaves gaps after ties, so RANK = 2 finds nobody. DENSE_RANK numbers distinct values without gaps.

Solution 4: Correlated Subquery

Before window functions, the classic answer was: a salary is the Nth highest if exactly N distinct salaries are greater than or equal to it.

SELECT DISTINCT e1.salary
FROM employee e1
WHERE 3 = (
    SELECT COUNT(DISTINCT e2.salary)
    FROM employee e2
    WHERE e2.salary >= e1.salary
);

Result: 6500, the third highest salary.

It works in any database, but the subquery runs for every row, so on a large table it is slow. Mention it as an alternative, and use DENSE_RANK in practice.

Solution 5: The Nth Highest in Each Group

The usual follow-up: "the second highest salary in each department". Add PARTITION BY to the window function:

WITH ranked AS (
    SELECT department, name, salary,
           DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
    FROM employee
)
SELECT department, name, salary
FROM ranked
WHERE salary_rank = 2
ORDER BY department;

Result: HR – Grace (4800), IT – Eve (6500), Sales – Alice (5000).

In Sales, Bob and Carol share the top salary, so the second highest is Alice's 5000. A department with only one distinct salary would be missing from the result.

Which Solution to Choose

In an interview, start with DENSE_RANK, explain why not ROW_NUMBER or RANK, and mention how you would return NULL.

Practice

• Rank Employee Salaries
• Top Earners by Department
• Highest Replacement Cost Movies

Key Takeaways:

• The second highest value is MAX(x) WHERE x < (SELECT MAX(x) ...); it handles ties and returns NULL when there is none.
• With LIMIT 1 OFFSET N-1, always use DISTINCT, or ties shift the answer.
• A scalar subquery turns "no rows" into NULL.
• DENSE_RANK() solves any N, returns the employees, and with PARTITION BY works per group.
• ROW_NUMBER and RANK give wrong answers when values repeat.

Frequently Asked Questions

How do I find the second highest salary in SQL?

The shortest portable query is SELECT MAX(salary) FROM employee WHERE salary < (SELECT MAX(salary) FROM employee);. It returns the largest salary below the top one, ignores ties and returns NULL when everyone earns the same.

How do I find the Nth highest salary in SQL?

Use DENSE_RANK() OVER (ORDER BY salary DESC) in a CTE and filter on rank N, or SELECT DISTINCT salary ... ORDER BY salary DESC LIMIT 1 OFFSET N-1. Both handle duplicate salaries correctly.

Why not use ROW_NUMBER or RANK?

ROW_NUMBER numbers equal salaries differently, so a tie at the top pushes the same value into second place. RANK skips numbers after ties: with two top salaries, there is no rank 2 at all. DENSE_RANK numbers distinct values consecutively.

How do I return NULL if there is no Nth highest salary?

Put the query inside a scalar subquery: SELECT (SELECT DISTINCT salary FROM employee ORDER BY salary DESC LIMIT 1 OFFSET 9) AS result;. When the inner query finds nothing, the outer query still returns one row with NULL.