Trusted answers to developer questions
Trusted Answers to Developer Questions

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
RELATED COURSES

View all Courses

Keep Exploring