Find 2nd Highest Salary in SQL

In this article, we will look at a popular question **How to find 2nd Highest Salary in SQL** asked in many interviews.

Table of Contents

• Problem
• Solution
  • Using Subquery to find second highest salary of Employee
  • Using keywords such as limit and top to find 2nd highest salary of Employee
    • Using LIMIT in MYSQL
    • Using TOP in SQL Server
    • Using ROWNUMBER in Oracle

Problem

Given a Table of Employees with Employee Details we have to find the Employee with the 2^nd Highest Salary. We have to print the Employee details of the Employee or we can just print the Salary.

Let us Consider a Table Employees with the following attributes and records:

ID	NAME	SALARY
1	Amanda Jones	12000
2	Steve Madden	15000
3	Robert Henry	10000
4	Shawn Adams	10000
5	Luke Shaw	11000

The Employees table contains Attributes ID, NAME and SALARY for each Employee. In the given relation, the Employee ‘Amanda Jones’ has 2^nd Highest Salary with value 12000.

Now let us look at the way to find the Employee with 2nd Highest Salary using SQL query. Before that let us first create the schema for the table above.

CREATE TABLE Employees ( ID INTEGER, NAME VARCHAR(20), SALARY INTEGER );

1 2 3 4 5 6 7 8

CREATE TABLE Employees ( ID INTEGER, NAME VARCHAR(20), SALARY INTEGER );

Let us enter some sample records into our Table Employees.

INSERT INTO Employees VALUES(1,'Amanda Jones',12000); INSERT INTO Employees VALUES(2,'Steve Madden',15000); INSERT INTO Employees VALUES(3,'Robert Henry',10000); INSERT INTO Employees VALUES(4,'Shawn Adams',10000); INSERT INTO Employees values(5,'Luke Shaw',11000);

1 2 3 4 5 6 7

INSERT INTO Employees VALUES(1,'Amanda Jones',12000); INSERT INTO Employees VALUES(2,'Steve Madden',15000); INSERT INTO Employees VALUES(3,'Robert Henry',10000); INSERT INTO Employees VALUES(4,'Shawn Adams',10000); INSERT INTO Employees values(5,'Luke Shaw',11000);

Note: If using SQL Server, run GO Command after CREATE TABLE Block and all INSERT Command operations . One more point to note if we make the ID as key identity we don’t need to provide ID explicitly in each INSERT Statement; just the name and salary needs to be provided..

We will discuss different approaches to provide solution for the problem statement.

Solution

There are multiple ways to find second highest salary in SQL.

Using Subquery to find second highest salary of Employee

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

SELECT MAX(SALARY) FROM Employees;

1 2 3

SELECT MAX(SALARY) FROM Employees;

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 :

SELECT MAX(SALARY) FROM Employees WHERE SALARY < (SELECT MAX(SALARY) FROM Employees);

1 2 3

SELECT MAX(SALARY) FROM Employees WHERE SALARY < (SELECT MAX(SALARY) FROM Employees);

For the table above, this query will give Output:

12000

1 2 3

12000

If we want to print the Employee Details having the 2^nd Highest Salary, we execute this query (only in MYSQL) :

SELECT ID,NAME,SALARY FROM Employees WHERE SALARY < (SELECT MAX(salary) FROM Employees) ORDER BY SALARY DESC LIMIT 1;

1 2 3

SELECT ID,NAME,SALARY FROM Employees WHERE SALARY < (SELECT MAX(salary) FROM Employees) ORDER BY SALARY DESC LIMIT 1;

Output:

The ORDER BY DESCclause is used to arranges the employees in Order of Decreasing Salaries and then LIMIT 1 gets us the first value from that arrangement. The LIMIT keyword is used mainly in MYSQL Servers only.

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

SELECT MAX(SALARY) FROM Employees WHERE SALARY NOT IN (SELECT MAX(SALARY) FROM Employees);

1 2 3

SELECT MAX(SALARY) FROM Employees WHERE SALARY NOT IN (SELECT MAX(SALARY) FROM Employees);

Output:

12000

1 2 3

12000

Just like we used MAX Function, we can also use the MIN function in MYSQL Server to find out 2^nd Highest Salary. The query would be like this :

SELECT MIN(SALARY) FROM (SELECT SALARY FROM Employees ORDER BY SALARY DESC LIMIT 2);

1 2 3

SELECT MIN(SALARY) FROM (SELECT SALARY FROM Employees ORDER BY SALARY DESC LIMIT 2);

Further reading:

Difference between where and having in SQL

Read more →

Join 3 or more tables in SQL

Read more →

Using keywords such as limit and top to find 2nd highest salary of Employee

Using LIMIT in MYSQL

This method is to write a general single line query to find N^th Highest Salary for any relation or table. Using the concept of LIMIT and OFFSET, we can get the desired result. Let us have a look at the query:

On executing this we get details of the employee too.

Output:

Let us look how this works:

• At first, the Salary of all employees is arranged in decreasing order using ORDER BY DESC command.
• Next the LIMIT command has two arguments first is the OFFSET i.e. this indicates after how many rows records are fetched, second is the LIMIT which tells how many records are to be fetched.
• So, after Arranging Salaries in decreasing order, after skipping 1 row we fetch 1 record which is the salary of ‘Amanda Jones’.

This query is also helpful in finding N^th Highest Salary from this table. The generalized version of this query is:

The Offset is always N-1 to find N^th Highest Salary with limit also as 1. Hence, if we need to find 4^th Highest Salary, Offset will be 3 and limit as always 1.This query will not work if there is only 1 Row/Record in our table, it will throw error. Minimum Records are required to be 2.

Note: The above method discussed is viable only in MYSQL Server. LIMIT is not supported in ORACLE and SQL Server.

Using TOP in SQL Server

Still, if we want to fetch records on the basis of a limit we can use TOP Command in SQL Server. The query for the said command looks familiar to:

The output for this query will be:

Explanation: 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 2^nd Highest Salary.

Using ROWNUMBER in Oracle

Similarly, in ORACLE Server, if we want to get records on the basis of Ordering the data first then fetching them row-wise we can use the following query:

Output:

Explanation: We use DENSE_RANK() Function which provides contiguous sequence of ranks to rows obtained after arranging the SALRY Column in descending order. We give it the alias name ROWNUMBER and fetch the record at ROWNUMBER = 2 which gives 2^nd Highest Salary.

So that’s it for the article, we looked at different ways to solve **How to find 2nd Highest Salary in SQL** problem. You can create the schema or table shown above on your database and try executing the queries shown above in any SQL Compiler for practice.