Javatpoint Logo
Javatpoint Logo

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

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

Nth Highest salary

The Output Screen

Nth Highest salary

Let us understand how this query is working:

As this query is nested query lets understand each part step by step:

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:

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:

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:

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

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

Nth Highest salary

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:

  1. First task is to Identify the employee having TOP n non similar(distinct) salary.
  2. Calculate the minimum salary among all the salaries resulted from above query, by doing this we get nth highest salary.
  3. 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.


Next Topic#




Youtube For Videos Join Our Youtube Channel: Join Now

Help Others, Please Share

facebook twitter pinterest

Learn Latest Tutorials


Preparation


Trending Technologies


B.Tech / MCA