Related Tags

sql
communitycreator

# How to find the nth highest salary from a table using SQL

Khizar Hayat Saani

The problem of finding the nth highest salary from a table that contains employee salaries is quite popular.

While the problem may seem daunting at first, it is not too difficult to solve once the problem is clear.

The nth highest salary

### The problem

The problem of finding the nth highest salary is as follows:

Given a table that contains employee salaries for an organization, find the nth highest salary.

Here, n can take on any value. A value of 1 would mean we are tasked with finding the highest salary within the organization, 2 represents the second-highest salary, and so on.

### The solution

Let’s consider the following table:

Employee_Salaries
 EID ENAME SALARY 1 Harry 3000 2 Jane 3000 3 Sam 8000 4 Claire 5500 5 Bob 4000 6 Alice 4500 7 Henry 6000 8 Kim 2500 9 Tom 5000 10 Hannah 6500

### Step 1: Order the table

When finding the nth highest salary, the first step is to order the table in descending order with respect to salary.

This way, the highest salary will be on the top of the table and others will follow in order of decreasing salaries.

In SQL, this is can be written as follows:

select distinct salary from Employee_Salaries
order by salary desc

To cater to repeating salaries, such as 3000 in the above table, we can add the keyword distinct to ensure that our result table contains no duplicate values.

The table now looks like this:

A
 SALARY 8000 6500 6000 5500 5000 4500 4000 3000 2500

### Step 2: Find the nth highest salary

After Step 1, we obtained a table (A) that contains all the salaries sorted in descending order.

This means that the nth highest salary is on the nth row of A. There are many ways you could access the nth row exactly. We will use the min function and the limit keyword. The nth highest salary is the minimum salary among the top n rows of A.

We can write the SQL as follows:

select min(salary)
from A limit n

The min function returns the minimum value for a column in a table.

The limit function returns the first n rows.

This will return the nth highest salary.

### Step 3: Putting it all together

Now, we can move on to combining the above queries to form the final query that returns the nth highest salary.

The query becomes (n = 5):

select min(salary)
from (
select distinct salary
from Employee_Salaries
order by salary desc
limit 5
)

### Code

CREATE TABLE Employee_Salaries(
ID int PRIMARY KEY,
Name varchar(20),
Salary int
);

INSERT INTO Employee_Salaries
VALUES (1, 'Harry', 3000);
INSERT INTO Employee_Salaries
VALUES (2, 'Jane', 3000);
INSERT INTO Employee_Salaries
VALUES (3, 'Sam', 8000);
INSERT INTO Employee_Salaries
VALUES (4, 'Claire', 5500);
INSERT INTO Employee_Salaries
VALUES (5, 'Bob', 4000);
INSERT INTO Employee_Salaries
VALUES (6, 'Alice', 4500);
INSERT INTO Employee_Salaries
VALUES (7, 'Henry', 6000);
INSERT INTO Employee_Salaries
VALUES (8, 'Kim', 2500);
INSERT INTO Employee_Salaries
VALUES (9, 'Tom', 5000);
INSERT INTO Employee_Salaries
VALUES (10, 'Hannah', 6500);

SELECT min(Salary) FROM (
SELECT distinct Salary from Employee_Salaries
order by Salary desc
limit 5 # This is n
) AS A

RELATED TAGS

sql
communitycreator

CONTRIBUTOR

Khizar Hayat Saani
RELATED COURSES

View all Courses

Keep Exploring

Learn in-demand tech skills in half the time