How to find Nth highest salary?

Finding the Nth highest salary is a very common SQL interview question. There are multiple ways to do it depending on your database.

1. Using ORDER BY + LIMIT (MySQL / PostgreSQL)

To find the Nth highest salary:

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

Example (3rd highest salary):

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

2. Using Subquery (Generic SQL)

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

This works for fixed N (like 3rd), but not scalable for dynamic N.

3. Using DENSE_RANK() (Best & Recommended)

SELECT salary
FROM (
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
    FROM employees
) t
WHERE rank = N;

Example (2nd highest salary):

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

4. Using ROW_NUMBER() (When duplicates matter)

SELECT salary
FROM (
    SELECT salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
    FROM employees
) t
WHERE rn = N;

Difference (Important for Interview)

• DENSE_RANK() → Handles duplicates (same salary gets same rank)
• ROW_NUMBER() → Always unique ranking
• RANK() → Skips ranks when duplicates exist

Pro Tip (Interview Answer)

If interviewer asks:

“Best way?”

Answer:

Use DENSE_RANK() → clean, scalable, handles duplicates correctly