How to find second highest salary in SQL
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 2nd, 3rd and Nth highest salary in SQL
- How to find 2nd,3rd 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 3rd 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