SQL Query to Find Second Highest Salary

Finding the second highest salary from an employee table is a classic SQL problem the job seekers often face in their interview. Database programmers also have to solve similar problem like finding N^th highest number from a table. Finding the maximum salary from an Employee table is trivial. But finding the second highest is a tricky one. We can solve this problem in DBMS independent way or DBMS specific ways – like LIMIT in MySQL, or TOP in SQL Server.

Let’s consider this example employee table.

In this table, there are four employees with different salaries. Employee John has the highest salary 3514 but we have to find out the second highest salary which is 3501 (salary of employee Robert).

Database (DBMS) Independent Way:

Here we’ll not use any DBMS specific keywords. The following SQL statements should work with any relational database.

Solution 1:

SELECT MAX(salary) FROM employee WHERE salary < (SELECT MAX(salary) FROM employee)

The logic here is to select the maximum salary from all the salaries that are less than the maximum salary. All salaries that are less than the maximum salary contain the second highest one as maximum.

Solution 2:

SELECT MAX(salary) FROM employee WHERE salary NOT IN (SELECT MAX(salary) FROM employee);

Similar logic is implemented using NOT IN clause. The logic here is to select the maximum salary from the salaries that are not the maximum salary.

Second Highest Salary in MySQL

We can solve this problem by using MySQL keyword LIMIT. LIMIT keyword basically limits the number of rows returned by a query. LIMIT is often used in conjunction with ORDER BY clause.

SELECT salary FROM (SELECT salary FROM employee ORDER BY salary DESC LIMIT 2) as tmp ORDER BY salary LIMIT 1

The subquery is sorted by salary in descending order and limited by 2. That means that it will have only two rows – highest salary in first row and the second highest salary in the second row. The subquery is then sorted by salary in ascending order. So the second highest salary will become the first row. As the query is limited by one – that means it will return the first row that has the second highest salary.

Second Highest Salary in SQL Server

SQL Server has similar keyword TOP to limit number of returned rows though syntax is slightly different.

The same MySQL logic is implemented here with TOP keyword.

SELECT TOP 1 salary FROM (SELECT TOP 2 FROM employee ORDER BY salary DESC) as tmp ORDER BY salary ASC

If you also want to contribute, click here.