How to find second highest salary in SQL - with example -DBMS Tutorial

Table of Contents

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

find second highest salary in SQL — find the second highest 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)

Finding the highest salary

We can simply use the Max() function as shown below.

Select Max(Salary) from Employee;

To get the second highest salary 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 an incorrect result in the 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 the 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 the third highest salary in SQL using a subquery

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

How to find the second-highest salary in MySQL using the LIMIT keyword

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

SELECT salary FROM employees WHERE salary NOT IN (SELECT MAX(salary) FROM employees) ORDER BY salary DESC LIMIT 1;

This query selects the salary column from the employee’s table, filters out the highest salary by using a subquery to select the maximum salary and then excluding it with the NOT IN operator, and then sorts the remaining salaries in descending order and returns the top (or second highest) salary using the LIMIT clause.

How to find the 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)

I hope this helps! Let me know if you have any questions.

For other interesting technical blog Articles- What is TRP’s full form?