Find Nth Highest Salary in MySQL

A classic interview question

"Find the Nth highest salary" is one of the most asked SQL interview questions. It tests your understanding of sorting, LIMIT, OFFSET and duplicates. There are a few clean ways to solve it — LIMIT/OFFSET, and DENSE_RANK on modern MySQL.

The second highest

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

Sort salaries from highest to lowest, then OFFSET 1 skips the top one, and LIMIT 1 returns the next — the second highest.

The Nth highest with OFFSET

-- Nth highest: OFFSET is N-1
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 2;   -- 3rd highest

For the Nth highest, use OFFSET N-1. To get the 3rd highest, offset 2. This general pattern finds any rank you want.

Handling duplicate salaries

DISTINCT is important. If three employees all earn the top salary, they would fill three rows, and the offset would skip within the same salary. DISTINCT collapses equal salaries into one, so "second highest" means the second distinct salary, which is what people usually mean.

The DENSE_RANK way

SELECT salary FROM (
    SELECT salary,
        DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
) t
WHERE rnk = 2;   -- 2nd highest

In MySQL 8.0+, DENSE_RANK() gives each salary a rank, with equal salaries sharing a rank and no gaps. Filtering rnk = N reads very clearly and handles ties naturally.

The subquery method

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

For the second highest specifically, this classic subquery finds the highest salary below the maximum. It is neat for N=2, though LIMIT/OFFSET or DENSE_RANK scale better for any N.

Common mistakes

• Forgetting DISTINCT. Duplicate salaries then break the offset.
• Off-by-one in OFFSET. The Nth highest uses OFFSET N-1.
• Using DENSE_RANK on old MySQL. Window functions need MySQL 8.0+.
• Forgetting DESC. Without it you get the Nth lowest, not highest.

Summary

• Nth highest: ORDER BY salary DESC LIMIT 1 OFFSET N-1 with DISTINCT.
• Second highest uses OFFSET 1.
• DISTINCT collapses tied salaries so ranks are correct.
• DENSE_RANK (MySQL 8.0+) ranks salaries and reads clearly.
• A subquery with MAX handles the second highest neatly.

Next, group and rank data with GROUP BY and HAVING, or remove duplicates in deleting duplicate rows.