Find nth Highest/Minimum Salary from Table

Write a Query to get Highest/Minimum Salary from Table. Also, a query to find nth  Highest/Minimum Salary where n can be any number?

Maximum/Minimum/Average Salary from Table- 

Easiest way to solve this problem is to use Aggregate Functions. This can be done by using MAX/MIN/AVG Functions available in Oracle. MAX function will return the Maximum value for the column used in function and MIN will return the Minimum value for that column.

Consider below Table data and use Aggregate functions to find Maximum, Minimum and Average salary from the selected Group.

 SQL> Select * From TEST;  
     ID NAME      SALARY STATUS  
 ---------- ---------- ---------- ----------  
     102 Ankit      8000 Active  
     104 Nikhil     69000 Active  
     105 Rajan      18000 InActive  
     107 Karan     101000 Active  
     110 Sajal      88000 InActive  
     101 Ravi      89000 Active  
     109 Manu      777000 InActive  
 7 rows selected.  

 SQL> SELECT MAX(SALARY), MIN(SALARY), AVG(SALARY) From TEST;  
 MAX(SALARY) MIN(SALARY) AVG(SALARY)  
 ----------- ----------- -----------  
    777000    8000 164285.714  

Following Query can be used in case you need Employee Details also in your result set for the highest and lowest paid Employee.

 SQL> SELECT * From TEST  
  2 Where Salary IN(SELECT MAX(SALARY) From Test);  
     ID NAME      SALARY STATUS  
 ---------- ---------- ---------- ----------  
     109 Manu      777000 InActive  

 SQL> SELECT * From TEST  
  2 Where Salary IN(SELECT MIN(SALARY) From Test);  
     ID NAME      SALARY STATUS  
 ---------- ---------- ---------- ----------  
     102 Ankit      8000 Active  

Nth Highest and Minimum Salary- 

What if we want to get the second max salary from Table and then third and so on. There are different ways of doing this. Let’s see few examples with the below Table Data.

 SQL> SELECT * From TEST;  
     ID NAME      SALARY STATUS  
 ---------- ---------- ---------- ----------  
     102 Ankit      8000 Active  
     104 Nikhil     69000 Active  
     105 Rajan      18000 InActive  
     107 Karan     101000 Active  
     110 Sajal      88000 InActive  
     101 Ravi      89000 Active  
     109 Manu      777000 InActive  
 7 rows selected.  

Second Highest Salary:

What if we try to exclude the highest salary value from the result set returned by the SQL. If we remove the highest salary from a group of salary values, then we will have a new group of values whose highest salary is actually the 2nd highest in the original table.

Below is the SQL Query to find Second highest salary from the Group.

 SQL> SELECT MAX(SALARY) Second_Max_Salary From TEST  
  2 Where SALARY NOT IN(SELECT MAX(SALARY) From TEST);  
 SECOND_MAX_SALARY  
 -----------------  
       101000  

The SQL above first finds the highest salary value in the table using “(Select MAX(Salary) from TEST)”. Then, adding the “WHERE Salary NOT IN” in front basically creates a new set of Salary values that does not include the highest Salary value.

Third Highest Salary:

We can use the TOP keyword (for databases that support the TOP keyword, like SQL Server)

Select Max(Salary) from Test

Where

Salary Not In(Select Distinct Top 2 Salary From Test Order By Salary Desc);

In Oracle, we can do this with the below methods.

Nth Maximum Salary:

Using Subquery: 

Below SQL can be used to find Nth Highest salary where n is the number which indicates the number of employee in the Selected group.

Select * from Test T1

Where (n-1)=(Select count(Distinct(T2.Salary)) from Test T2 where T1.Salary<=T2.Salary);

The subquery in the SQL above is actually a specific type of subquery known as correlated subquery. The reason it is called a correlated subquery is because the subquery uses a value from the outer query in its WHERE clause. In this case that value is the T1 table alias as we pointed out earlier. A normal subquery can be run independently of the outer query, but a correlated subquery can NOT be run independently of the outer query.

Let’s use above Query to find 3rd/5th Max Salary.

 SQL> SELECT * From TEST T1  
  2 Where 3=(SELECT Count(Distinct(T2.SALARY)) From TEST T2 Where T1.SALARY<=T2.SALARY);  
     ID NAME      SALARY STATUS  
 ---------- ---------- ---------- ----------  
     101 Ravi      89000 Active  

 SQL> SELECT * From TEST T1  
  2 Where 5=(SELECT Count(Distinct(T2.SALARY)) From TEST T2 Where T1.SALARY<=T2.SALARY);  
     ID NAME      SALARY STATUS  
 ---------- ---------- ---------- ----------  
     104 Nikhil     69000 Active  

Using Row_Number() :  

You can also use Analytical Functions to fin Max Salary. Following is, the Query to find Max Salaries from Table using ROW_NUMBER().

 SQL> SELECT * From  
  2 (SELECT TT.*,  
  3  ROW_NUMBER() Over (Order By SALARY DESC) Rn  
  4  From TEST TT  
  5 )  
  6 Where Rn=3;  
     ID NAME      SALARY STATUS       RN  
 ---------- ---------- ---------- ---------- ----------  
     101 Ravi      89000 Active       3 

 SQL> SELECT * From  
  2 (SELECT TT.*,  
  3  ROW_NUMBER() Over (Order By SALARY DESC) Rn  
  4  From TEST TT  
  5 )  
  6 Where Rn=5;  
     ID NAME      SALARY STATUS       RN  
 ---------- ---------- ---------- ---------- ----------  
     104 Nikhil     69000 Active       5  

Using Rank

The Rank function will assign a ranking to each row starting from 1. The query is actually quite similar to the one where we used the Row_Number() analytic function, and works in the same way as well. 
So, we can use below SQL in Oracle to find the nth highest salary using the RANK function:

 SQL> SELECT * From  
  2 (SELECT TT.*,  
  3  RANK() Over (Order By SALARY DESC) Rn  
  4  From TEST TT  
  5 )  
  6 Where Rn=3;  
     ID NAME      SALARY STATUS       RN  
 ---------- ---------- ---------- ---------- ----------  
     101 Ravi      89000 Active       3  

 SQL> SELECT * From  
  2 (SELECT TT.*,  
  3  RANK() Over (Order By SALARY DESC) Rn  
  4  From TEST TT  
  5 )  
  6 Where Rn=5;  
     ID NAME      SALARY STATUS       RN  
 ---------- ---------- ---------- ---------- ----------  
     104 Nikhil     69000 Active       5  

Nth Minimum Salary:

Using Subquery: 

Below SQL can be used to find Nth minimum salary where n is the number which indicates the number of employee in the Selected group.

Select * from Test T1

Where (n-1)=(Select count(Distinct(T2.Salary)) from Test T2 where T1.Salary>=T2.Salary);

The subquery in the SQL above is actually a specific type of subquery known as correlated subquery. The reason it is called a correlated subquery is because the subquery uses a value from the outer query in its WHERE clause. In this case that value is the T1 table alias as we pointed out earlier. A normal subquery can be run independently of the outer query, but a correlated subquery can NOT be run independently of the outer query.

Let’s use above Query to find 3rd/5th Min Salary.

 SQL> SELECT * From TEST T1  
  2 Where 3=(SELECT Count(Distinct(T2.SALARY)) From TEST T2 Where T1.SALARY>=T2.SALARY);  
     ID NAME      SALARY STATUS  
 ---------- ---------- ---------- ----------  
     104 Nikhil     69000 Active  
 SQL> SELECT * From TEST T1  
  2 Where 5=(SELECT Count(Distinct(T2.SALARY)) From TEST T2 Where T1.SALARY>=T2.SALARY);  
     ID NAME      SALARY STATUS  
 ---------- ---------- ---------- ----------  
     101 Ravi      89000 Active  

Using Row_Number() :  

You can also use Analytical Functions to fin Min Salary. Following is, the Query to find Min Salaries from Table using ROW_NUMBER().

 SQL> SELECT * FROM TEST;  
     ID NAME      SALARY STATUS  
 ---------- ---------- ---------- ----------  
     102 Ankit      8000 Active  
     104 Nikhil     69000 Active  
     105 Rajan      18000 InActive  
     107 Karan     101000 Active  
     110 Sajal      88000 InActive  
     101 Ravi      89000 Active  
     109 Manu      777000 InActive  
 7 rows selected.  
 SQL> SELECT * From  
  2   (SELECT TT.*,  
  3   ROW_NUMBER() Over (Order By SALARY) Rn  
  4   From TEST TT  
  5   )  
  6   Where Rn=3;  
     ID NAME      SALARY STATUS       RN  
 ---------- ---------- ---------- ---------- ----------  
     104 Nikhil     69000 Active       3  
 SQL> SELECT * From  
  2   (SELECT TT.*,  
  3   ROW_NUMBER() Over (Order By SALARY) Rn  
  4   From TEST TT  
  5 )  
  6 Where Rn=5;  
     ID NAME      SALARY STATUS       RN  
 ---------- ---------- ---------- ---------- ----------  
     101 Ravi      89000 Active       5  

Using Rank

The Rank function will assign a ranking to each row starting from 1. The query is actually quite similar to the one where we used the Row_Number() analytic function, and works in the same way as well. 
So, we can use below SQL in Oracle to find the nth minimum salary using the RANK function:

 SQL> SELECT * From  
  2   (SELECT TT.*,  
  3   RANK() Over (Order By SALARY) Rn  
  4   From TEST TT  
  5   )  
  6 Where Rn=3;  
     ID NAME      SALARY STATUS       RN  
 ---------- ---------- ---------- ---------- ----------  
     104 Nikhil     69000 Active       3  

 SQL> SELECT * From  
  2   (SELECT TT.*,  
  3   RANK() Over (Order By SALARY) Rn  
  4   From TEST TT  
  5   )  
  6 Where Rn=5;  
     ID NAME      SALARY STATUS       RN  
 ---------- ---------- ---------- ---------- ----------  
     101 Ravi      89000 Active       5  

Let's work on the below Exercise.

Display Employee Information Department wise

SELECT * FROM

  (SELECT TT.*,

          Rank() over (PARTITION BY Department ORDER BY Salary DESC) Rn

   FROM Test TT)

WHERE Rn= n;   --Change value to required nth Rank.

Let’s use above Query to find highest paid Employee in each Department

 SQL> SELECT * From TEST Order By Department, Salary;  
     ID NAME      SALARY STATUS   DEPARTMENT  
 ---------- ---------- ---------- ---------- --------------------  
     110 Sajal      88000 InActive  BFSI  
     101 Ravi      89000 Active   BFSI  
     107 Karan     101000 Active   BFSI  
     102 Ankit      8000 Active   CISCO  
     109 Manu      777000 InActive  CISCO  
     105 Rajan      18000 InActive  Insurance  
     104 Nikhil     69000 Active   Retail  
     103 Tom       5600 Active   SFDC  
     106 Tomy       7700 InActive  SFDC  
     108 Jack      88800 Active   Security  
 10 rows selected.  

 SQL> SELECT * FROM  
  2  (SELECT TT.*,  
  3   Rank() over (PARTITION BY Department ORDER BY Salary DESC) Rn  
  4   FROM Test TT)  
  5 WHERE Rn=1;  
     ID NAME      SALARY STATUS   DEPARTMENT          RN  
 ---------- ---------- ---------- ---------- -------------------- ----------  
     107 Karan     101000 Active   BFSI               1  
     109 Manu      777000 InActive  CISCO             1  
     105 Rajan      18000 InActive  Insurance         1  
     104 Nikhil     69000 Active   Retail             1  
     106 Tomy       7700 InActive  SFDC               1  
     108 Jack      88800 Active   Security            1  
 6 rows selected.  

In the same way 2nd/3rd/Nth Highest paid Employee in each Department

 SQL> SELECT * FROM  
  2  (SELECT TT.*,  
  3   Rank() over (PARTITION BY Department ORDER BY Salary DESC) Rn  
  4   FROM Test TT)  
  5 WHERE Rn=2;  
     ID NAME      SALARY STATUS   DEPARTMENT          RN  
 ---------- ---------- ---------- ---------- -------------------- ----------  
     101 Ravi      89000 Active   BFSI             2  
     102 Ankit      8000 Active   CISCO             2  
     103 Tom       5600 Active   SFDC             2  

 SQL> SELECT * FROM  
  2  (SELECT TT.*,  
  3   Rank() over (PARTITION BY Department ORDER BY Salary DESC) Rn  
  4   FROM Test TT)  
  5 WHERE Rn=3;  
     ID NAME      SALARY STATUS   DEPARTMENT          RN  
 ---------- ---------- ---------- ---------- -------------------- ----------  
     110 Sajal      88000 InActive  BFSI  

You can use any of the above methods based on your requirement.

Get involved and leave your Comments in the Box Below. The more people get involved, the more we all benefit.So, leave your thoughts before you leave the page.