How to Find Second Highest Salary in SQL command

 

How to Find Second Highest Salary in SQL

 

In this article, we will look up a frequently asked SQL interview question. So the problem statement is that given a table of employees with their names and salary.

The task is to find the second highest salary on that table. For the purpose of understanding, let’s create a table using the below SQL command:

-- create a table named employees  CREATE TABLE employees (    name TEXT NOT NULL,    salary INTEGER NOT NULL  );

 

The above code will create an empty table named employees with two rows namely, name and salary. 

Now let’s insert some data to the table so that we can make queries to the table using the below SQL commands:

-- insert data into employees table  INSERT INTO employees VALUES ('Amar', 200000);  INSERT INTO employees VALUES ('Akbhar', 5000000)  INSERT INTO employees VALUES ('Anthony', 7000000);  INSERT INTO employees VALUES ('Mike', 6000000)  INSERT INTO employees VALUES ('Harvey', 4000000);

 

To check out the employees table at this point use the below SQL command:

 

 

 

 

SELECT * FROM employees;

 

 

 

 

 

Output:

 

 

Clearly, Mike has the 2nd highest salary in this case. But as the data set becomes bigger, merely observing the data and filtering the 2nd highest salary can be hectic. Therefore coming back to the problem statement let’s design an SQL query for finding out the second highest salary in the employees table. 

Now, we will see the steps how to find 2nd highest salary in SQL.

 

SQL Query to Find the Second Highest Salary. 

Below is the list of methods that can be used to find the second highest salary. 

Method 1: Excluding the highest Salary

This method involves making a subquery that excludes the maximum salary from the query dataset and finds the next highest salary in the new data set after exclusion of the highest salary that technically is the second highest salary in the employees table. Use the below SQL query for the same: 

 

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

 

Output:

 

Method 2: Using Correlated SubQuery

This method makes use of correlated subqueries. This solution can also be used to get the Nth highest salary in the employees table.  The basic idea is that for each processed record by the outer query, the inner query gets executed and returns the records with a salary less than that of the current salary. In our case we need the second highest salary, so we will stop our query as soon as the query inside returns the value 2. 

 

SELECT salary FROM employees e WHERE 2 = (SELECT COUNT(DISTINCT salary) FROM employees p WHERE e.salary<=p.salary);

 

 

Output:

 

Method 3: Using LIMIT Clause

This method involves making use of the LIMIT query to get the second highest salary from employees table. The LIMIT clause can be used to query the first few rows, last few rows, or rows within a range. Here we will also be using the SORT BY clause to sort the query set.  So the SQL query goes as follows: 

SELECT salary FROM (SELECT salary FROM employees ORDER BY salary DESC LIMIT 2) AS emp ORDER BY salary LIMIT 1;

 

Output:

 

 

Conclusion: 

In this article, we explored the following methods to find the second highest salary in a table of employees:

·         Excluding the highest salary from the query set.

·         Using correlated subquery

·         Using the LIMIT clause