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)
2nd
Highest and 2nd Lowest salary (using sub query)
2nd 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)
|
2nd 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)
3rd or 4th 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
2nd 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 3rd, 4th
and 5th 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 2nd 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 3rd, 4th
and 5th 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 2nd 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!!!
