How to Find the Second Highest Salary in MySQL

Hello folks, I hope you are doing well. In this article, I am about to teach you how to find the second highest salary in MySQL with the help of the example. This question is one of the most asked questions by interviewers. Being a developer, SQL developer, Data Engineer, and Data Analyst, You must have knowledge about to find the second highest salary in MySQL table.

Throughout this article, we will cover multiple ways like LIMIT, ROW_NUMBER, and Sub Query to find the second highest salary of the employee. If you are going to attend an interview for a Developer, SQL Developer, Data Engineer, and Data Analysis position then this can be a high chance aks to ask this question the interviewer to test your SQL skills.

Throughout this article, I will give you a generic solution to find the second-highest salary that will fit in any kind of database like MySQL, Microsoft SQL Server, PostgreSQL, and Oracle.

Headings of Contents

1 Data
2 How to Find the Second Highest Salary in MySQL
3 Summary

Data

I have created a table employee having some records for demonstration of this article. I will apply all the operations on this table in order to find the second highest salary of the employee with the help of the proper example and explanation.

+------------+-----------+-----------+
| first_name | last_name | salary    |
+------------+-----------+-----------+
| Abhishek   | Mishra    | 120000.00 |
| David      | Russel    | 105000.00 |
| Manish     | Das       |  90000.00 |
| Mahesh     | Kumar     |  85340.00 |
| Ajay       | Gupta     |  85340.00 |
| John       | Doe       |  80000.00 |
| Vaishanvi  | Mehta     |  75250.00 |
| Nishant    | Chahal    |  65000.00 |
| Mahima     | Chaudhari |  40300.00 |
| Arushi     | Shulka    |  40300.00 |
| Darshan    | Natrajan  |  40000.00 |
| Vinay      | Kumar     |  35000.00 |
| Minakshi   | Kumary    |  30000.00 |
| Akash      | Saini     |  25500.00 |
| Vishvajit  | Rao       |  25000.00 |
| Ayush      | Shukla    |  23500.00 |
| Harshita   | Singh     |  20000.00 |
+------------+-----------+-----------+

As we know, In the above table, The employee David has the second highest salary 105000.00 after Abhishek. But we will find this salary through SQL query.

let’s explore all the ways to get the second-highest salary for the employee.

How to Find the Second Highest Salary in MySQL

Here, We will see multiple ways to find the second-highest salary in MySQL with the help of the proper examples. You can use any one of them to get the second-highest salary.
We will explore concepts of SQL Subquery, Operators, LIMIT, OFFSET, ROW_NUMBER(), and Aggregate Functions to get the second-highest salary.

Second Highest Salary in MySQL Using NOT IN Operator

This is the generic way to find the second highest salary of the employee that will work in any kind of RDBMS like MYSQL, PostgreSQL, Microsoft SQL Server, and Oracle.
In this SQL query, I have used the concept of SQL Sub Query, MAX Aggregate function, and NOT IN operator.


SELECT 
    MAX(salary) AS 'second highest salary'
FROM
    employee
WHERE
    salary NOT IN (SELECT 
            MAX(salary)
        FROM
            employee)

Find the Second Highest Salary in MySQL — **Second Highest Salary in MySQL Using NOT IN Operator**

Explanation of the above code:

First I have written a SQL Sub Query that will return the maximum salary of the table employee. Sub Query is given below. Sub query is also called an inner query.


SELECT 
    MAX(salary)
FROM
    employee

Second, I have written an outer query or parent query that will return the maximum salary but the returned salary shouldn’t be included in the result of the inner query. The Outer query is given below.


SELECT 
    MAX(salary) AS 'second highest salary'
FROM
    employee
WHERE
    salary NOT IN

Find Second Highest Salary using Less Than Operator

In the above query, I Have used the NOT IN operator to find the second highest salary of the employee but in this example, I will just replace the NOT IN operator with less than operator ( < ).


SELECT 
    MAX(salary) AS 'second highest salary'
FROM
    employee
WHERE
    salary < (SELECT 
            MAX(salary)
        FROM
            employee);

Find the Second Highest Salary using the ORDER BY and LIMIT clause

We have another way to find the second highest salary of the employee by using the LIMIT clause and ORDER BY clause. Here, I have used THE concept of SQL Subquery along with ORDER BY and LIMIT Clause. The ORDER BY clause is sued to order the result set in ascending or descending order and the LIMIT clause is used to limit the number of records to be returned.

SELECT 
    first_name, last_name, salary
FROM
    employee
WHERE
    salary < (SELECT 
            MAX(salary)
        FROM
            employee)
ORDER BY salary DESC
LIMIT 1;

Explanation of the above SQL Query:

First, I found the maximum salary of the employee table in the inner query.
Second, I have fetched all the records from the employee table whose salary is less than the maximum salary returned by the inner query.
Third, Order the result set on the basis of the salary column in descending order using the ORDER BY clause.
Fourth, Limit the result set by one.

Find the second highest salary of the employee using LIMIT and OFFSET

You can use MySQL LIMIT and OFFSET to get the second highest salary of the employee. As we know that limit has an optional parameter called offset which always represents how many records from starting should be hidden.

To get the second highest salary of the employee we will set the value of limit and offset with 1.

Let’s see.

SELECT 
    first_name, last_name, salary
FROM
    employee
ORDER BY salary DESC
LIMIT 1, 1;

In the above query, I have ordered the result set in descending order by using the ORDER BY clause and using the concept of the LIMIT and OFFSET.
The first parameter of the LIMIT represented the number of records that should be hidden and the second value represents the number of records that should be fetched from the row where the offer ends the hidden part.

Find the second highest salary in MySQL using ROW_NUMBER()

The ROW_NUMBER() is a function in MySQL that comes under the MySQL window functions. ROW_NUMBER() function does not take any parameter, It is used to assign the unique row number to each record in a table.

select 
	id,
	first_name, 
	last_name, 
	salary, 
row_number() over (order by salary desc) as 'second highest salary' 
from employee 
limit 1, 1;

👉Learn MySQL Window Functions:- Click Here

Find the second-highest salary in MySQL using CTE

In MySQL, CTE stands for Common Table Expression. CTE in MySQL is used to define a temporary named result set that will temporarily be available in the execution scope of the statements such as SELECT, INSERT, UPDATE, or DELETE. CTE always starts with WITH keyword.CTE can also be used to find the second-highest salary of the employee in MySQL.

with second_highest_salary as 
(
select 
	id,
	first_name, 
	last_name, 
	salary, 
row_number() over (order by salary desc) as 'RowNumber' 
from employee 
)
select * from second_highest_salary where RowNumber=2;

Related MySQL Articles:

Summary

So in this article, we have seen all about how to find the second-highest salary in MySQL with the help of various such as using LIMIT, ORDER BY, ROW_NUMBER, NOT IN Operator, OFFSET Clause, etc along with examples. This question is asked by most of the interviewers in developer, SQL Developer, Data Analyst, and Data Engineers interviews, Specially, I have faced this question multiple times during interviews. You can use any one of them to find the second highest salary of the employee.

Especially I would recommend you, Always go with solution number 2 and 3 because they are very easy to remember during the interviews.

If an interviewer asks this during this interview, Then you can use any one of them at your convenience. All the solutions are valid and generic.

If you found this article helpful, Please share and keep visiting for further MySQL tutorials.

Have a nice day …..

◀How to Get Last Day of the Month in MySQL

How to Get a Substring from a String in MySQL▶