# How to Find the Nth Highest Salary in SQL ?

#### Cyber Security : Go from Zero to Hero

Most Popular

60 Lectures 5.5 hours

#### Master C and Embedded C Programming- Learn as you go

Best Seller

66 Lectures 5.5 hours

#### C Programming from scratch- Master C Programming

Best Seller

60 Lectures 8 hours

Finding the nth highest salary in SQL is a common interview question and a practical task in database management. Here are several approaches to solve this problem, each with detailed explanations and examples. We'll assume we have a table Employee with columns id, name, and salary.

ID Name Salary
1 Alice 60000
2 Bob 70000
3 Charlie 80000
4 David 70000
5 Eve 90000

### 1. Using Subquery with DISTINCT and LIMIT/OFFSET

This approach involves using a subquery to first sort the salaries in descending order, then limiting the results to find the nth highest salary.

Example:

SELECT DISTINCT salary
FROM Employees
ORDER BY salary DESC
LIMIT 1 OFFSET n-1;

Explanation:

1. SELECT DISTINCT salary : Select unique salaries from the Employees table.
2. ORDER BY salary DESC : Sort the salaries in descending order.
3. LIMIT 1 OFFSET n-1 : Skip the first n-1 salaries and then return the next one. The LIMIT 1 ensures only one result is returned.

For example, to find the 3rd highest salary (n = 3):

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

### 2. Using ROW_NUMBER() Window Function

The ROW_NUMBER() window function assigns a unique sequential integer to rows within a partition of a result set.

Example:

SELECT salary
FROM (
SELECT salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM Employees
) AS ranked_salaries
WHERE row_num = n;

Explanation:

1. Subquery : The subquery generates a list of salaries with a row number assigned to each salary in descending order.

• ROW_NUMBER() OVER (ORDER BY salary DESC) : Assigns a unique row number to each salary, ordered by salary in descending order.

2. Main Query : The main query filters the results to return the salary where the row number equals n.

For example, to find the 3rd highest salary (n = 3):

SELECT salary
FROM (
SELECT salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM Employees
) AS ranked_salaries
WHERE row_num = 3;

### 3. Using DENSE_RANK() Window Function

The DENSE_RANK() function assigns ranks to rows in an ordered partition, where the same rank is assigned to rows with equal values.

Example:

SELECT salary
FROM (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
FROM Employees
) AS ranked_salaries
WHERE rank = n;

Explanation:

1. Subquery : The subquery generates a list of salaries with a rank assigned to each salary in descending order.

• DENSE_RANK() OVER (ORDER BY salary DESC) : Assigns ranks to each salary, with the same rank for equal salaries.

2. Main Query : The main query filters the results to return the salary where the rank equals n.

For example, to find the 3rd highest salary (n = 3):

SELECT salary
FROM (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
FROM Employees
) AS ranked_salaries
WHERE rank = 3;

### 4. Using CROSS JOIN with DISTINCT and COUNT()

This method involves self-joining the table and using COUNT() to find the nth highest salary.

Example:

SELECT DISTINCT e1.salary
FROM Employees e1
WHERE n-1 = (
SELECT COUNT(DISTINCT e2.salary)
FROM Employees e2
WHERE e2.salary > e1.salary
);

Explanation:

1. SELECT DISTINCT e1.salary : Select unique salaries from the Employees table (alias e1).
2. Subquery : The subquery counts the number of distinct salaries in the table (alias e2) that are greater than the current salary in e1.
3. WHERE n-1: The main query filters for the salary in e1 where there are exactly n-1 salaries greater than it.

For example, to find the 3rd highest salary (n = 3):

SELECT DISTINCT e1.salary
FROM Employees e1
WHERE 2 = (
SELECT COUNT(DISTINCT e2.salary)
FROM Employees e2
WHERE e2.salary > e1.salary
);

### 5. Using NTILE() Window Function

The NTILE() function distributes the rows in an ordered partition into a specified number of groups.

Example:

SELECT salary
FROM (
SELECT salary, NTILE(n) OVER (ORDER BY salary DESC) AS ntile_rank
FROM Employees
) AS ntile_salaries
WHERE ntile_rank = 1
ORDER BY salary ASC
LIMIT 1;

Explanation:

1. Subquery : The subquery partitions the salaries into n groups and assigns a ntile_rank to each group.

• NTILE(n) OVER (ORDER BY salary DESC)**: Divides the rows into n groups.

2. Main Query : The main query filters for the group with ntile_rank = 1 (the top group) and returns the lowest salary in that group.

• ORDER BY salary ASC LIMIT 1 : Orders the top group by salary in ascending order and returns the smallest salary (i.e., the nth highest salary).

For example, to find the 3rd highest salary (n = 3):

SELECT salary
FROM (
SELECT salary, NTILE(3) OVER (ORDER BY salary DESC) AS ntile_rank
FROM Employees
) AS ntile_salaries
WHERE ntile_rank = 1
ORDER BY salary ASC
LIMIT 1;

Each of these approaches has its own strengths and is suitable for different scenarios. The choice of method can depend on the specific SQL database being used, the size of the dataset, and performance considerations.