How to Find the Nth Highest Salary in SQL (3 Methods)

Introduction

Finding the Nth highest salary is one of the most common SQL interview questions. There are three main approaches, each with different trade-offs.

Method 1: DENSE_RANK Window Function (Best)

The most reliable method. DENSE_RANK handles ties correctly — if two people have the same salary, they get the same rank.

-- Find the 3rd highest salary:
SELECT salary FROM (
  SELECT salary,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
  FROM employees
) ranked
WHERE rank = 3;

-- Find the Nth highest (parameterized):
SELECT DISTINCT salary FROM (
  SELECT salary,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
  FROM employees
) ranked
WHERE rank = @N;  -- Replace @N with any number

Method 2: LIMIT OFFSET (MySQL & PostgreSQL)

Use DISTINCT + ORDER BY + LIMIT/OFFSET for a simpler approach.

-- MySQL: 3rd highest salary (N-1 = 2):
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 2;

-- PostgreSQL (same syntax):
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 2;

-- SQL Server: use TOP with subquery:
SELECT MIN(salary) FROM (
  SELECT DISTINCT TOP 3 salary
  FROM employees
  ORDER BY salary DESC
) AS top_salaries;

Method 3: Correlated Subquery (Classic Interview Answer)

The traditional approach without window functions. Works in all databases but is slower.

-- Find salary where exactly N-1 salaries are higher:
SELECT DISTINCT salary
FROM employees e1
WHERE 3 - 1 = (
  SELECT COUNT(DISTINCT salary)
  FROM employees e2
  WHERE e2.salary > e1.salary
);

-- Explanation:
-- For the 3rd highest: count salaries above it = 2 (3-1)
-- For the 2nd highest: count salaries above it = 1 (2-1)

Find Nth Highest Per Department

A common extension: find the highest salary within each group.

-- Top 3 salaries per department:
SELECT * FROM (
  SELECT
    name, department, salary,
    DENSE_RANK() OVER (
      PARTITION BY department
      ORDER BY salary DESC
    ) AS dept_rank
  FROM employees
) ranked
WHERE dept_rank <= 3
ORDER BY department, dept_rank;

Handle Edge Cases

What if there is no Nth highest salary? Or if multiple employees share the same salary?

-- Return NULL if Nth doesn't exist:
SELECT COALESCE(
  (SELECT DISTINCT salary
   FROM (
     SELECT salary,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
     FROM employees
   ) ranked
   WHERE rank = 5),
  NULL
) AS fifth_highest_salary;

-- Get all employees at the Nth salary level:
WITH salary_ranks AS (
  SELECT *, DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
  FROM employees
)
SELECT name, salary FROM salary_ranks WHERE rank = 3;

Best Practices

• Use DENSE_RANK for correctness with ties
• LIMIT OFFSET is simplest for MySQL/PostgreSQL
• Know the correlated subquery method for interviews
• Always handle edge cases (NULL when Nth doesn't exist)
• For "top N per group", combine DENSE_RANK with PARTITION BY

Generate SQL Queries with AI2SQL

Skip the syntax lookup. Describe what you need in plain English and AI2SQL generates the correct SQL query instantly.

Frequently Asked Questions