SQL Query to Find Nth Highest Salary

If you have already learned how to find the second highest salary in SQL, the next natural question and one of the most popular SQL interview questions of all time is:

“How do you find the Nth highest salary in SQL?”

Where N can be any number e.g. the 3rd highest, the 5th highest, the 10th highest, or any position you need.

This question is asked in almost every SQL interview because it tests several important skills at once: your understanding of ranking functions, subqueries, CTEs, and how SQL handles duplicate values.

In this guide, we will walk through every major method to find the Nth highest salary in SQL, explain exactly how each one works, compare their strengths and weaknesses, and show you which approach to use in different situations.

Why Is This Question Important?

Before diving into the solutions, it is worth understanding why this question matters beyond interviews.

Finding the Nth highest value is a real-world requirement that comes up constantly in data analysis:

• Finding the 3rd best-performing salesperson in a region
• Identifying the 5th most expensive product in a catalog
• Pulling the 10th largest transaction for audit purposes
• Ranking students and retrieving those at specific positions

Mastering this concept gives you a strong foundation in SQL ranking and filtering. These skills that apply far beyond salary queries.

Setting Up the Example Table

We will use the same employees table throughout this guide so you can follow along easily.

sql

CREATE TABLE employees (
    id INT,
    name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

INSERT INTO employees VALUES
(1, 'Alice', 'Engineering', 95000),
(2, 'Bob', 'Engineering', 85000),
(3, 'Charlie', 'Marketing', 75000),
(4, 'Diana', 'Marketing', 75000),
(5, 'Eve', 'Sales', 65000),
(6, 'Frank', 'Sales', 55000),
(7, 'Grace', 'Engineering', 95000),
(8, 'Henry', 'HR', 45000);

The distinct salary values in descending order are:

Rank	Salary
1st	95000
2nd	85000
3rd	75000
4th	65000
5th	55000
6th	45000

Notice that 95000 appears twice (Alice and Grace) and 75000 appears twice (Charlie and Diana). Handling duplicates correctly is one of the most important parts of this problem.

For all examples, our goal will be to find the 3rd highest salary which should return 75000.

Method 1: Using DENSE_RANK() Window Function (Recommended)

This is the most reliable, most readable, and most professional approach. It is the method you should reach for first in any real-world project.

sql

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

Result:

nth_highest_salary
75000

How It Works

1. The inner query assigns a dense rank to every row, ordered by salary from highest to lowest
2. DENSE_RANK() gives duplicate salaries the same rank and does not skip numbers, so 95000 gets rank 1, 85000 gets rank 2, and 75000 gets rank 3
3. The outer query filters for rnk = 3 to return the 3rd highest salary

Why DENSE_RANK and Not RANK or ROW_NUMBER?

This is a critical distinction that many beginners miss.

Function	How It Handles Duplicates	Ranks for our data
ROW_NUMBER()	Assigns unique numbers — no ties	1, 2, 3, 4, 5, 6, 7, 8
RANK()	Same rank for ties, skips next rank	1, 1, 3, 3, 5, 6, 7, 8
DENSE_RANK()	Same rank for ties, no skipping	1, 1, 2, 3, 3, 4, 5, 6

With RANK(), the 3rd rank is skipped because two salaries share rank 1. This means your query would return no results for N=3 even though a clear 3rd highest salary exists.

With ROW_NUMBER(), duplicate salaries get different row numbers, so the result depends on arbitrary ordering and may not reflect the true Nth highest distinct salary.

DENSE_RANK() is always the correct choice for finding the Nth highest salary.

To Find Any Nth Highest Salary

Simply change the number in the WHERE clause:

sql

-- 5th highest salary
WHERE rnk = 5

-- 10th highest salary
WHERE rnk = 10

Database Compatibility

Works in: MySQL 8+, PostgreSQL, SQL Server, Oracle

Method 2: Using LIMIT and OFFSET (MySQL / PostgreSQL)

This is the cleanest and most readable approach for MySQL and PostgreSQL users.

sql

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

Result:

nth_highest_salary
75000

How It Works

1. ORDER BY salary DESC sorts all salaries from highest to lowest
2. DISTINCT removes duplicate salary values so each unique salary appears once
3. LIMIT 1 returns only one row
4. OFFSET 2 skips the first 2 rows (the 1st and 2nd highest salaries) and starts from the 3rd

The OFFSET Formula

To find the Nth highest salary, the offset is always N – 1:

N	OFFSET
1st highest	OFFSET 0
2nd highest	OFFSET 1
3rd highest	OFFSET 2
5th highest	OFFSET 4
10th highest	OFFSET 9

General Formula for Nth Highest

sql

SELECT DISTINCT salary AS nth_highest_salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET (N-1);

Database Compatibility

Works in: MySQL, PostgreSQL Does NOT work in: SQL Server, Oracle (use FETCH NEXT instead)

Method 3: Using a Subquery with TOP (SQL Server)

If you are working in SQL Server, the TOP keyword combined with a subquery is a classic approach.

sql

SELECT MIN(salary) AS nth_highest_salary
FROM (
    SELECT DISTINCT TOP 3 salary
    FROM employees
    ORDER BY salary DESC
) top_salaries;

Result:

nth_highest_salary
75000

How It Works

1. The inner query selects the top 3 distinct salaries in descending order, returning 95000, 85000, and 75000
2. The outer query takes the minimum of those top 3 values — which is 75000, the 3rd highest

General Formula for Nth Highest

Replace TOP 3 with TOP N:

sql

SELECT MIN(salary) AS nth_highest_salary
FROM (
    SELECT DISTINCT TOP N salary
    FROM employees
    ORDER BY salary DESC
) top_salaries;

Database Compatibility

Works in: SQL Server Does NOT work in: MySQL, PostgreSQL, Oracle

Method 4: Using a Correlated Subquery

This is a classic approach that works across almost all SQL databases and is frequently asked about in interviews even though it is slower on large datasets.

sql

SELECT DISTINCT salary AS nth_highest_salary
FROM employees e1
WHERE 2 = (
    SELECT COUNT(DISTINCT salary)
    FROM employees e2
    WHERE e2.salary > e1.salary
);

Result:

nth_highest_salary
75000

How It Works

1. For each salary in e1, the inner query counts how many distinct salaries in e2 are strictly greater than it
2. When that count equals N – 1 (which is 2 for the 3rd highest), exactly two salaries are higher. This means the current salary is the Nth highest
3. WHERE 2 = (...) filters for exactly this condition

General Formula

To find the Nth highest salary, use N – 1 in the WHERE condition:

sql

-- 3rd highest: WHERE 2 = ...
-- 5th highest: WHERE 4 = ...
-- 10th highest: WHERE 9 = ...

Database Compatibility

Works in: MySQL, PostgreSQL, SQL Server, Oracle.

Method 5: Using a CTE (Common Table Expression)

CTEs make complex queries more readable by breaking them into named steps. This is a clean, professional approach that is easy to read and maintain.

sql

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

Result:

nth_highest_salary
75000

How It Works

1. The CTE ranked_salaries assigns a dense rank to every salary in descending order
2. The main query simply selects from that CTE where the rank equals N
3. The logic is identical to Method 1 but the CTE makes it significantly more readable

Why Use a CTE?

CTEs shine in production environments where:

• Other team members need to read and understand your queries
• You need to add more logic on top of the ranked data
• You want to avoid deeply nested subqueries that are hard to debug

Database Compatibility

Works in: MySQL 8+, PostgreSQL, SQL Server, Oracle

Method 6: Using FETCH NEXT (SQL Server / Oracle)

SQL Server and Oracle support the FETCH NEXT syntax as an alternative to LIMIT OFFSET.

sql

-- SQL Server / Oracle syntax
SELECT DISTINCT salary AS nth_highest_salary
FROM employees
ORDER BY salary DESC
OFFSET 2 ROWS
FETCH NEXT 1 ROW ONLY;

Result:

nth_highest_salary
75000

How It Works

This works exactly like LIMIT OFFSET. It skip N-1 rows and fetch the next 1 row. The syntax is just different for SQL Server and Oracle compatibility.

Database Compatibility

Works in: SQL Server 2012+, Oracle 12c+ Does NOT work in: MySQL (use LIMIT OFFSET instead)

Finding the Nth Highest Salary Per Department

In real-world scenarios, you often need the Nth highest salary within each department — not just across the entire table. Window functions make this straightforward.

sql

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

Result — 2nd highest salary per department:

name	department	salary	dept_rank
Bob	Engineering	85000	2
Diana	Marketing	75000	2
Frank	Sales	55000	2

How It Works

PARTITION BY department resets the ranking for each department. So instead of ranking all salaries together, SQL ranks salaries independently within each department giving you the Nth highest salary per group.

Handling Edge Cases

What If N Is Larger Than the Number of Distinct Salaries?

If you ask for the 10th highest salary but only 6 distinct salaries exist, your query should return NULL or an empty result and not an error.

All the methods above handle this gracefully by returning an empty result set. If you specifically want NULL instead of no rows, wrap your query:

sql

SELECT (
    SELECT DISTINCT salary
    FROM employees
    ORDER BY salary DESC
    LIMIT 1 OFFSET 9
) AS nth_highest_salary;

This returns NULL if no 10th salary exists, which is cleaner for reporting purposes.

What If the Salary Column Contains NULL Values?

Always filter out NULLs to avoid unexpected results:

sql

WITH ranked_salaries AS (
    SELECT salary,
           DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
    WHERE salary IS NOT NULL
)
SELECT salary AS nth_highest_salary
FROM ranked_salaries
WHERE rnk = 3;

Method	Works On	Handles Duplicates	Beginner Friendly	Performance	Best For
DENSE_RANK()	MySQL 8+, PostgreSQL, SQL Server, Oracle	Yes	Intermediate	Good	Production queries
LIMIT OFFSET	MySQL, PostgreSQL	Yes (with DISTINCT)	Yes	Good	MySQL/PostgreSQL projects
TOP + MIN Subquery	SQL Server	Yes	Yes	Moderate	SQL Server projects
Correlated Subquery	All databases	Yes	Complex	Slow on large data	Learning SQL concepts
CTE + DENSE_RANK	MySQL 8+, PostgreSQL, SQL Server, Oracle	Yes	Readable	Good	Team environments
FETCH NEXT	SQL Server, Oracle	Yes (with DISTINCT)	Yes	Good	SQL Server/Oracle projects

Real-World Use Cases

Compensation Benchmarking

HR teams use Nth highest salary queries to understand pay bands. Finding the 5th or 10th percentile salary within a department to ensure competitive compensation structures.

Sales Leaderboards

Finding the 2nd or 3rd highest performing salesperson by revenue. It is useful for tiered bonus structures where rewards go beyond just the top performer.

Fraud Detection

Identifying the Nth largest transaction in a time period to flag unusual patterns. For example, finding the 5 largest wire transfers in a month for audit review.

Inventory and Pricing

E-commerce platforms use similar queries to find the Nth most expensive product in a category for dynamic pricing and competitive analysis.

Academic Rankings

Schools and universities use ranking queries to retrieve students at specific rank positions e.g. finding the 10th highest GPA for scholarship cutoff decisions.

Advantages and Disadvantages of Each Method

DENSE_RANK() / CTE + DENSE_RANK

Advantages: Handles duplicates perfectly, scalable, easy to adapt for any N or department-level ranking Disadvantages: Requires knowledge of window functions, not supported in MySQL below version 8

LIMIT OFFSET

Advantages: Very readable, easy to modify for any N, clean syntax Disadvantages: Not available in SQL Server or Oracle without modification

TOP + MIN Subquery

Advantages: Works well in SQL Server, intuitive logic Disadvantages: SQL Server only, less readable than CTE approach

Correlated Subquery

Advantages: Works on almost all databases, great for learning Disadvantages: Very slow on large tables, hard to read and maintain

FETCH NEXT

Advantages: Standard SQL syntax, works well in SQL Server and Oracle Disadvantages: Not supported in MySQL

Common Mistakes to Avoid

• Using ROW_NUMBER() instead of DENSE_RANK() — ROW_NUMBER treats duplicate salaries as separate rows and can return wrong results
• Using RANK() instead of DENSE_RANK() — RANK() skips numbers after ties, which means some Nth values may never be returned
• Forgetting DISTINCT with LIMIT OFFSET — Without DISTINCT, duplicate salaries count as separate rows and the offset gives incorrect results
• Not handling NULL salaries — Always filter NULLs explicitly to avoid unexpected ranking behavior
• Using the wrong OFFSET value — Remember the formula: OFFSET = N – 1, not N
• Using a database-specific syntax on the wrong database — Always check which SQL dialect you are using before choosing your method

Quick Reference Cheat Sheet

Goal	Recommended Query Pattern
Nth highest (any DB)	CTE + DENSE_RANK WHERE rnk = N
Nth highest (MySQL/PostgreSQL)	SELECT DISTINCT … ORDER BY DESC LIMIT 1 OFFSET N-1
Nth highest (SQL Server)	SELECT MIN FROM (SELECT DISTINCT TOP N … ORDER BY DESC)
Nth highest per department	DENSE_RANK with PARTITION BY department
Handle NULLs	Add WHERE salary IS NOT NULL before ranking
Return NULL if N exceeds count	Wrap query in SELECT (…) AS nth_highest_salary

Finding the Nth highest salary in SQL is one of those questions that starts simple but reveals a lot about how deeply you understand SQL. Ranking functions, duplicate handling, subqueries, CTEs, and database-specific syntax all come into play.

Here is a simple decision framework to choose the right method:

• Working on any modern database and want the best approach → Use CTE + DENSE_RANK()
• Working on MySQL or PostgreSQL and want the simplest syntax → Use LIMIT OFFSET
• Working on SQL Server without window functions → Use TOP + MIN subquery
• Need it to work on any database and prefer classic SQL → Use Correlated Subquery
• Need Nth highest within groups → Use DENSE_RANK() with PARTITION BY

No matter which method you use, always remember to handle duplicates with DENSE_RANK, filter NULL values, and test your query against edge cases where N exceeds the number of available salaries.

FAQs