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
