How to find second highest salary in SQL

Table of Contents

To find the second highest salary in SQL is a very common important interview question.

If you are going for the role at the junior level. There are many ways to find the second highest salary in SQL.

Some important questions to find the salary are given below.

How to find 2^nd, 3^rd and Nth highest salary in SQL
How to find 2^nd,3^rd and Nth lowest salary in SQL

Employees table for this demo

Create table Employee
(
     ID number(10) primary ,
     FirstName varchar(50),
     LastName varchar(50),
     Gender varchar(50),
     Salary number(10)
);

Insert into Employee values (‘Ben’, ‘Hoskins’, ‘Male’, 70000)
Insert into Employee values (‘Mark’, ‘Hastings’, ‘Male’, 60000)
Insert into Employee values (‘Steve’, ‘Pound’, ‘Male’, 45000)
Insert into Employee values (‘Ben’, ‘Hoskins’, ‘Male’, 70000)
Insert into Employee values (‘Philip’, ‘Hastings’, ‘Male’, 45000)
Insert into Employee values (‘Mary’, ‘Lambeth’, ‘Female’, 30000)
Insert into Employee values (‘Valarie’, ‘Vikings’, ‘Female’, 35000)
Insert into Employee values (‘John’, ‘Stanmore’, ‘Male’, 80000)

To find the highest salary it is straight forward. We can simply use the Max() function as shown below.

Select Max(Salary) from Employee;

To get the second highest salary to use a subquery along with Max() function as shown below.
Select Max(Salary) from Employee where Salary < (Select Max(Salary) from Employee)
This query gives incorrect result in case of 3^rd highest salary

SELECT MAX(salary) FROM Employee WHERE Salary NOT IN ( SELECT Max(Salary) FROM Employee);

To find nth highest salary using Sub-Query

SELECT TOP 1 SALARY

FROM (

SELECT DISTINCT TOP N SALARY

FROM EMPLOYEE

ORDER BY SALARY DESC

) RESULT

ORDER BY SALARY

How to find 2nd highest salary using Correlated subquery or

how to find second highest salary in SQL using a subquery

SELECT Id, Salary FROM Employee e WHERE 2=(SELECT COUNT(DISTINCT Salary) FROM Employee p WHERE e.Salary<=p.Salary)

How to find second highest salary in MySQL using LIMIT keyword

SELECT Salary FROM (SELECT Salary FROM Employee ORDER BY salary DESC LIMIT 2) AS Emp ORDER salary ASC

How to find second highest salary in SQL Server Top keyword

SELECT TOP 1 Salary FROM ( SELECT TOP 2 Salary FROM Employee ORDER BY Salary DESC) Emp ORDER salary ASC

Similarly, we can find out the second-lowest salary of an employee by simple update the existing commands like-

Select Min(Salary) from Employee where Salary > (Select Min(Salary) from Employee)

For other interesting technical blog Article click here