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

 In This tutorial we will discuss about how to get 2nd highest salary in employee table. Below are the different ways:

EmployeeDetail Table:

Using sub-query (With < Operator):

First, we find the employee with highest salary. To do this we run this query:

Select max(Salary) from EmployeeDetail

This will give us the Maximum Salary; we can further nest this query to a subquery to find the Second Highest Salary. This query will work on MYSQL, ORACLE as well as SQL Server :

Using sub-query (With Not In Operator): 

We can also use the NOT IN Clause instead of comparing the salary between two sets.

Using correlated sub-query:

The distinct keyword is there to deal with duplicate salaries in the table. In order to find the Nth highest salary, we are only considering unique salaries. The highest salary means no salary is higher than it, the Second highest means only one salary is higher than it, 3rd highest means two salaries are higher than it, similarly Nth highest salary means N-1 salaries are higher than it.

For each record processed by outer query, inner query will be executed and will return how many records has records has salary less than the current salary. If you are looking for second highest salary then your query will stop as soon as inner query will return 2.

Using TOP Clause keyword:

We can use TOP Command in SQL Server. The sub-query produces a result set containing the salary of employees arranged in decreasing order and fetches TOP 2 records from the set; we use DISTINCT (can be ignored) to segregate duplicate values (if any). Then from RESULT of sub-query, we order the two rows in Ascending order the get the Topmost row which gives us the 2nd Highest Salary.

Please find below all the queries as per above method:

Using sub-query (With < Operator):

2nd Highest Salary
Select max(Salary) As Salary from EmployeeDetail where 
Salary < (Select max(Salary) from EmployeeDetail

3rd Highest Salary
Select max(Salary) As Salary from EmployeeDetail where 
Salary < (Select max(Salary) from EmployeeDetail Where
Salary < (Select max(Salary) from EmployeeDetail))

Using sub-query (With Not In Operator)
Select max(Salary) As Salary from EmployeeDetail where 
Salary NOT IN (Select max(Salary) from EmployeeDetail)


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


Using TOP Clause keyword
Select top 1 Salary From
(Select distinct top 2 Salary from 
EmployeeDetail Order by Salary Desc)
As Temp Order by Salary 

For detail explanation please watch below YouTube video: