How to find 2nd Lowest Salary | Find Nth Lowest Salary | 4 ways to get it

 EmployeeDetail table:

Four ways to get Nth lowest salary in EmployeeDetail table:

Select * from EmployeeDetail order by Salary 
---------------------1st Method----------------------------
--Using sub-query (With > Operator):
--2nd Highest Salary
Select min(Salary) As Salary from EmployeeDetail where 
Salary > (Select min(Salary) from EmployeeDetail)

--3rd Highest Salary
Select min(Salary) As Salary from EmployeeDetail where 
Salary > (Select min(Salary) from EmployeeDetail Where
Salary > (Select min(Salary) from EmployeeDetail))

---------------------2nd Method----------------------------
--Using sub-query (With Not In Operator)
Select min(Salary) As Salary from EmployeeDetail where 
Salary NOT IN (Select min(Salary) from EmployeeDetail)

---------------------3rd Method----------------------------
--Using correlated sub-query
Select distinct Salary from EmployeeDetail A
Where 2=(Select count(distinct salary) from EmployeeDetail B 
where A.Salary >= b.Salary)

---------------------4th Method-----------------------------
--Using TOP Clause keyword 
Select top 1 Salary From
(Select distinct top 2 Salary from 
EmployeeDetail Order by Salary)
As Temp Order by Salary DESC

/*NOTE: First three method won't consider NULL however 4th method 
will consider NULL values*/

Please find the below Youtube video:

No comments:

Post a Comment