Find the nth highest or lowest salary of the employee

In most of the sql server interview the interviewers are asking how to find the nth highest or lowest salary of the employee. This is very common and important question in sql server interview. There is several ways to find the nth highest or lowest salary. Here I am trying to give the simple and easy answer with example.

Ø  Using sub query

Ø  Using CET or Temp table

o   Using Rank function

o   Using Row Number function

I am using table which name is EMP.

(A)   Find the highest and lowest salary of the employee.

Highest salary

--Highest salary select MAX(Salary) as [Highest salary] from emp

Lowest salary

--Lowest salary select Min(Salary) as [Lowest salary] from emp

(B)    2^nd Highest and 2^nd Lowest salary (using sub query)

2^nd Highest salary

-- 2nd Highest salary select max(Salary) as [Highest salary] from emp where Salary < (select MAX(salary) from Emp) select top 1 salary as [Highest salary]  from Emp where Salary < (select MAX(salary) from Emp) order by Salary desc select max(Salary) as [Highest salary] from emp where Salary NOT IN (select MAX(salary) from Emp) select max(Salary) as [Highest salary] from emp where Salary <> (select MAX(salary) from Emp)

         

                   

2^nd lowest salary

select * from Emp order by Salary

     

-- 2nd Lowest salary select MIN(Salary) as [Lowest salary] from emp where Salary > (select MIN(salary) from Emp) select top 1 salary as [Lowest salary]  from Emp where Salary > (select MIN(salary) from Emp) order by Salary select MIN(Salary) as [Lowest salary] from emp where Salary NOT IN (select MIN(salary) from Emp) select MIN(Salary) as [Lowest salary] from emp where Salary <> (select MIN(salary) from Emp)

     

(C)   Top N Highest or Lowest salary

3 top highest salary

select distinct top 3 Salary  from Emp order by Salary desc

If you want to find the N top highest salary you can get it to replacing 3.

   

3 Lowest salaries

select distinct top 3 Salary  from Emp order by Salary

 

(D)   3^rd or 4^th  Highest or Lowest salary

Highest salary

-- 3rd salary select top 1 salary from emp where salary in (select distinct top 3* Salary  from Emp order by Salary desc) order by salary -- 4rd salary select top 1 salary from emp where salary in (select distinct top 4* Salary  from Emp order by Salary desc) order by salary

*you can replace the value as per your requirements.

     

 Lowest salary

-- 3rd lowest salary select top 1 salary from emp where salary in (select distinct top 3* Salary  from Emp order by Salary ) order by salary desc -- 4rd lowest salary select top 1 salary from emp where salary in (select distinct top 4* Salary  from Emp order by Salary ) order by salary desc

*you can replace the value as per your requirements.

   

(E)    Using  Dense Rank Function

2^nd highest salary

With Highest_Salary AS ( select salary, DENSE_RANK() over (order by salary desc) as Salary_Rank from Emp ) select Salary from Highest_Salary where Salary_Rank=2*

*you can get the n th highest salary.

   

Suppose if you want to find 3^rd, 4^th and 5^th highest salary use below query

--3rd,4th and 5th highest salary With Highest_Salary AS ( select salary, DENSE_RANK() over (order by salary desc) as Salary_Rank from Emp ) select Salary from Highest_Salary where Salary_Rank in (3,4,5)

Find the 2^nd lowest salary

--2nd Lowest salary With Lowest_Salary AS ( select salary, DENSE_RANK() over (order by salary ) as Salary_Rank from Emp ) select Salary from Lowest_Salary where Salary_Rank =2

     

Suppose if you want to find the 3^rd, 4^th and 5^th lowest salary

--3rd, 4th and 5th Lowest salary With Lowest_Salary AS ( select salary, DENSE_RANK() over (order by salary ) as Salary_Rank from Emp ) select Salary from Lowest_Salary where Salary_Rank in (3,4,5)

   

Suppose if you want to find the highest salary or lowest salary of the employee based on their location then we can achieve this using dense rank function with partition clause.

I am using below table

   

--Highest salary based on EmpAdd With Highest_Salary AS ( select EmpAdd,salary, DENSE_RANK() over (partition by EmpAdd order by salary desc ) as Salary_Rank from Emp ) select EmpAdd,Salary from Highest_Salary where Salary_Rank = 1 group by EmpAdd,Salary ; --2nd Highest salary based on EmpAdd With Highest_Salary AS ( select EmpAdd,salary, DENSE_RANK() over (partition by EmpAdd order by salary desc ) as Salary_Rank from Emp ) select EmpAdd,Salary from Highest_Salary where Salary_Rank =2 group by EmpAdd,Salary ;

   

We can also find the lowest and 2^nd lowest salary of the employee based by Employee address.

--Lowest salary based on Employee address With Lowest_Salary AS ( select EmpAdd,salary, DENSE_RANK() over (partition by EmpAdd order by salary ) as Salary_Rank from Emp ) select EmpAdd,Salary from Lowest_Salary where Salary_Rank =1 group by EmpAdd,Salary ; --2nd Lowest salary based on Employee address With Lowest_Salary AS ( select EmpAdd,salary, DENSE_RANK() over (partition by EmpAdd order by salary ) as Salary_Rank from Emp ) select EmpAdd,Salary from Lowest_Salary where Salary_Rank =2 group by EmpAdd,Salary ;

 

(F)    Using Row Number function

Row_Number():- It will give the row number. With the help of we can get the highest or lowest salary. But the problem with this method is it will work on only distinct column value (means no duplicate salary value). If table has duplicate value it will be not work. If you want to perform with duplicate data then first we need to create a temp table and store distinct data in table and from distinct we can easily find out highest or lowest salary. See in the given example below.

--Find the nth Highest salary create table #Highest_Salary(salary int) insert into #Highest_Salary select distinct salary from Emp ; Go With Final_Table as ( select salary,ROW_NUMBER() Over (Order by salary desc)as RowNumber from #Highest_Salary ) select salary from Final_Table where RowNumber=2(*) drop table #Highest_Salary

(*) you can put the number which you want to find the nth highest salary.

 

Nth lowest salary

--Find the nth lowest salary create table #Lowest_Salary(salary int) insert into #Lowest_Salary select distinct salary from Emp ; Go With Final_Table as ( select salary,ROW_NUMBER() Over (Order by salary )as RowNumber from #Lowest_Salary ) select salary from Final_Table where RowNumber=2 drop table #Lowest_Salary

Hope this article helps you. Thanks!!!