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 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.
Let’s consider the following table:
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 |
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:
SALARY |
8000 |
6500 |
6000 |
5500 |
5000 |
4500 |
4000 |
3000 |
2500 |
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 firstn
rows.
This will return the nth highest salary.
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
)
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
CONTRIBUTOR
View all Courses