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

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:

The SQL query to calculate second highest salary in database table name as Emp

Query: 2

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 Topic12 Codd?s Rules




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