Nth Highest salary
Finding the Nth highest salary( 2nd, 3rd, or nth highest) in a table is the most important and common question asked in various interviews.
Here we will show you the best and easiest way to write SQL queries to find nth highest salary in a table.
To show this, we are using Table Emp having employee details like EID, ENAME, and SALARY. Data present in the Emp Table is shown below:
Table Name: Emp
| EID | ENAME | SALARY |
|---|---|---|
| 1 | Amit | 20000 |
| 2 | Bhaskar | 30000 |
| 3 | Chandan | 25000 |
| 4 | Durgesh | 28000 |
| 5 | Parul | 30000 |
| 6 | Garima | 25000 |
| 7 | Akshita | 28000 |
| 8 | Sonu | 40000 |
| 9 | Ravi | 37000 |
| 10 | Rajesh | 320000 |
The SQL query to calculate second highest salary in database table name as Emp
Query: 1
(select distinct salary from emp order by salary desc)
where rownum < 3;
In order to calculate the second highest salary use rownum < 3
In order to calculate the third highest salary use rownum < 4
Output:
MIN(SALARY)
-----------
37000
The Structure and data in Emp Table
The Output Screen
Let us understand how this query is working:
Step 1: First this part of the query will get executed then the outer part of the query will act on the result produced by this query: select distinct salary from emp order by salary desc
As you can see that few employees are getting the same salary (for example Bhaskar, Parul, and Chandan, Garima are getting the same salary, therefore we have used distinct keyword, order by salary desc will arrange salary in descending order.
The output of select distinct salary from emp order by salary desc
SALARY
----------
40000
37000
32000
30000
28000
25000
20000
Step 2: SQL> select min(salary) from
(select distinct salary from emp order by salary desc)
where rownum < 3;
In step two we are applying the outer part of the nested query into the results we obtained from the internal query.
Select min(salary) from: will select min salary as 20000 which is not the second-highest salary, because of which we have used rownum < 3, the rownum < 3 will only give the number of rows from the top which is less than 3 i.e. 2.
The output of rownum< 3 will be:
SALARY
----------
40000
37000
Step 3: Now select min(salary).
The output will be:
SALARY
----------
37000
37000 is the second-highest salary.
Simillarly to find:
To find 3rd highest salary set rownum < 4
To find 4th highest salary set rownum < 5
And so on...
The SQL query to calculate second highest salary in database table name as Emp
Query: 2
select ename, salary, dense_rank()
over(order by salary desc)rank from Emp)
where rank = & num;
In order to calculate the second highest salary use num = 2
In order to calculate the third highest salary use num = 3
and so on...
Output:
ENAME SALARY Rank ------------ ---------- ---------- ravi 37000 2
Let us understand how this query is working:
Step 1: First this part of the query will get executed then the outer part of the query will act on the result produced by this query :
select ename, salary, dense_rank() over(order by salary desc)rank from Emp
dense_rank() calculates the rank of each row in an ordered group of rows and returns the rank as a number. The ranks start from integer 1 and so on in a consecutive manner.
If we talk about the above SQL query, based on the salary of the emp table the rank is returned. In case two or more than two rows have an equal salary, it assigns an equal rank to all the rows.
As you can see that few employees are getting the same salary(for example Bhaskar, Parul, and Chandan, Garima are getting the same salary, therefore we have used dense_rank(), order by salary desc will arrange salary in descending order.
Output of : select ename, salary, dense_rank() over(order by salary desc)rank from Emp
ENAME SALARY RANK
------------ ---------- ----------
sonoo 40000 1
ravi 37000 2
rohit 32000 3
bhaskar 30000 4
parul 30000 4
akshita 28000 5
durgesh 28000 5
garima 25000 6
chandan 25000 6
amit 20000 7
you can see from the output that Bhaskar, Parul are getting rank 4 as they both are getting equal salary) and Akshita, Durgesh are getting rank 4(as they both are getting equal salary) similarly Garima and Chandan.
Step 2: SQL> select * from(
select ename, salary, dense_rank()
over(order by salary desc)rank from Emp)
where r = &n;
In step 2 we are applying the outer part of the nested query into the result we obtained from the internal query.
Select * from: will select all the rows which are not the second-highest salary, because of which we have used r = &n, the r = &n will only give the matching rows according to the values entered by the user for n. if n = 2 resultant will be
Output for n = 2 will be:
Enter the value for n: 2
old 1: select * from(select ename, salary, dense_rank() over(order by salary desc)r from Emp) where r=&n
new 1: select * from(select ename, salary, dense_rank() over(order by salary desc)r from Emp) where r=2
ENAME SALARY R
------------ ---------- ----------
ravi 37000 2
To Find fourth highest salary:
Enter value for n: 4
old 1: select * from(select ename, salary, dense_rank() over(order by salary desc)r from Emp) where r=&n
new 1: select * from(select ename, salary, dense_rank() over(order by salary desc)r from Emp) where r=4
ENAME SALARY R
------------ ---------- ----------
bhaskar 30000 4
parul 30000 4
Similarly, to find:
To find 5th highest salary set n = 5
To find 6th highest salary set n = 6
And so on...
The SQL query to calculate second highest salary in database table name as Emp
Let's say the job is to calculate the Nth highest salary of employee from the above table. The procedure is as follows:
- First task is to Identify the employee having TOP n non similar(distinct) salary.
- Calculate the minimum salary among all the salaries resulted from above query, by doing this we get nth highest salary.
- From the result of above query, identify the details of the employee whose salary is the minimum salary.
Query No: 3
where salary IN (select distinct TOP N
salary from emp order by salary desc )
)
The above SQL query will find out the details of the emp with the nth highest salary.
Let's see the working of the above SQL query in detail:
- Consider n = 5.
The processing done by server is that, it starts with most inner query, the query: "select distinct TOP 5 salary from emp order by salary desc" will generate following result:
• 37000
• 32000
• 30000
• 28000
- The next outer query is: "select min(salary) from emp where salary IN (the result of a previous SQL query )". This will produce the following result:
From above result it is verified that the required Fifth highest salary is 28000.
- Lastly, the query which is outer most, is: "select * from emp where salary = result of previous SQL query ". The result of this query will be the details of employees having Fifth highest salary.
• ename salary
• ________________________
• akshita | 28000
• |
Working of query
As these queries are nested queries so this query involves the use of an inner query. There are two versions of Inner queries. Correlated and Uncorrelated queries. Uncorrelated queries are those where the inner query can run independently of the outer query, and the correlated query is those where the inner query runs in conjunction with the outer query. The query we took to calculate nth highest salary is an example of a correlated query.
Performance analysis of SQL query
From the above, we have learned that the inner query executes every time, single row of the outer query is processed, this ultimately brings a lot of performance overhead, especially when the number of rows is very large.
To avoid this, it is recommended to use Data Base specific keywords to get the result faster.
