SQL Query to Find Nth Highest Salary (With Example, ROW

SQL Query to Find Nth Highest Salary (With Example, ROW_NUMBER & 5 Easy Methods)

🔍 Introduction

SQL query to find Nth highest salary is one of the most commonly asked questions in SQL interviews and real-world database scenarios.

It helps you understand ranking, sorting, and filtering data effectively.

In this guide, you will learn:

✅ How to find Nth highest salary in SQL
✅ 5 different methods with examples
✅ Real interview solutions
✅ Step-by-step explanation

📌 What is Nth Highest Salary?

Nth highest salary means finding the salary that ranks at position N when salaries are sorted in descending order.

👉 Example:

Salary
10000
8000
6000
4000

1st highest = 10000
2nd highest = 8000
3rd highest = 6000

🧪 Sample Table

CREATE TABLE Employees (
Id INT,
Name VARCHAR(50),
Salary INT
);

INSERT INTO Employees VALUES
(1, ‘John’, 10000),
(2, ‘Amit’, 8000),
(3, ‘Sara’, 6000),
(4, ‘David’, 8000),
(5, ‘Alex’, 4000);

✅ Method 1: Using ROW_NUMBER() (Most Recommended)

SELECT Salary
FROM (
SELECT Salary,
ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum
FROM Employees
) t
WHERE RowNum = 2;

👉 Finds the 2nd highest salary

✅ Method 2: Using DENSE_RANK()

SELECT Salary
FROM (
SELECT Salary,
DENSE_RANK() OVER (ORDER BY Salary DESC) AS RankNum
FROM Employees
) t
WHERE RankNum = 2;

👉 Handles duplicate salaries correctly

✅ Method 3: Using TOP (SQL Server)

SELECT TOP 1 Salary
FROM (
SELECT DISTINCT TOP 2 Salary
FROM Employees
ORDER BY Salary DESC
) t
ORDER BY Salary ASC;

✅ Method 4: Using LIMIT & OFFSET (MySQL)

SELECT DISTINCT Salary
FROM Employees
ORDER BY Salary DESC
LIMIT 1 OFFSET 1;

👉 OFFSET = N-1

✅ Method 5: Using Subquery

SELECT MAX(Salary)
FROM Employees
WHERE Salary < (
SELECT MAX(Salary) FROM Employees
);

👉 Finds 2nd highest salary

🚀 Dynamic Query for Nth Highest Salary

SELECT Salary
FROM (
SELECT Salary,
DENSE_RANK() OVER (ORDER BY Salary DESC) AS RankNum
FROM Employees
) t
WHERE RankNum = N;

👉 Replace N with required value

⚠️ Important Points

Use DENSE_RANK() when duplicates exist
Use ROW_NUMBER() for unique ranking
Always sort using ORDER BY DESC
Test with sample data

🎯 Interview Questions

How to find 2nd highest salary in SQL?
Difference between ROW_NUMBER() and DENSE_RANK()?
How to handle duplicate salaries?
Write query for Nth highest salary

❓ Frequently Asked Questions

1. What is Nth highest salary in SQL?

Nth highest salary is the salary that appears at position N when sorted in descending order.

2. Which method is best to find Nth highest salary?

DENSE_RANK() is best when duplicates exist, while ROW_NUMBER() works well for unique values.

3. How to find 2nd highest salary in SQL?

You can use ROW_NUMBER(), DENSE_RANK(), or subquery methods to find the 2nd highest salary.

4. What is the difference between RANK and DENSE_RANK?

RANK skips numbers when duplicates exist, while DENSE_RANK does not skip ranks.

5. Can we find Nth highest salary without ranking functions?

Yes, using subqueries or LIMIT/OFFSET, but ranking functions are more efficient.