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
Output:
MIN(SALARY) ----------- 37000
The Structure and data in Emp Table
The Output Screen
Let us understand how this query is working:
The SQL query to calculate second highest salary in database table name as Emp
Query: 2
Output:
ENAME SALARY Rank ------------ ---------- ---------- ravi 37000 2
Let us understand how this query is working:
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
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:
- 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.
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.