MySQL query to find the second highest salary

By Vikas Kumar Last updated Jan 24, 2019 6

Write a MySQL query to find the second highest salary from the employees table. There are many ways to get second highest salary based upon which database you are using as different database provides different feature which can be used to find the second maximum salary from the employees table.

MySQL query to find the second highest salary

Consider an employees table, which is having the following records −

SELECT * FROM employees;

id	name	salary	department
1	Tom	4000	Technology
2	Sam	6000	Sales
3	Bob	3000	Technology
4	Alen	8000	Technology
5	Jack	12000	Marketing

For example, in above table, “Alen” has the second maximum salary as 8000.

Second maximum salary using sub query and IN clause

SELECT MAX(salary) 
FROM employees 
WHERE salary NOT IN ( SELECT Max(salary) FROM employees);

MAX(salary)
8000

Using sub query and < operator instead of IN clause

SELECT MAX(salary) 
From employees 
WHERE salary < ( SELECT Max(salary) FROM employees);

MAX(salary)
8000

Using the LIMIT clause in query

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

salary
8000

Salary

SELECT * FROM employees WHERE salary IN (SELECT Max(salary) FROM employees WHERE salary NOT IN (SELECT Max(salary) FROM employees));

SELECT * FROM employees Emp1 WHERE ( n ) = ( SELECT COUNT( DISTINCT ( Emp2.salary ) ) FROM employees Emp2 WHERE Emp2.salary >= Emp1.salary );

6 Comments

Jaykumar Patil says 6 years ago

What if two employees have same second highest salary and I also want to fetch other details like name, technology ?

1. Full Stack developer says 6 years ago
  That MySQL query will return all employees have same second highest salary with all employee details
```
SELECT * 
FROM   employees 
WHERE  salary IN (SELECT Max(salary) 
                  FROM   employees 
                  WHERE  salary NOT IN (SELECT Max(salary) 
                                        FROM   employees)); 
```
  More details : Find The Nth Highest Employee Salary From An Employee Table In MySql

Kamal Nayan says 6 years ago

What will you do for nth highest (say 10th)? Don’t use LIMIT.

Full Stack developer says 6 years ago

Use below MySql Query to find Nth highest employee salary from an employees table

Here, replace the n with any number. For example, if you have to find 10th highest salary , then replace n with 10 like below query –

SELECT *
FROM employees Emp1
WHERE 10 = (
                SELECT COUNT( DISTINCT ( Emp2.salary ) )
                FROM employees Emp2
                WHERE Emp2.salary >= Emp1.salary
            );

Rajesh says 6 years ago

How about

SELECT * FROM employees ORDER BY salary DESC limit 1,1;

1. Full Stack developer says 6 years ago
  
  you are right. thanks

MySQL query to find the second highest salary

MySQL query to find the second highest salary

Second maximum salary using sub query and IN clause

Using sub query and < operator instead of IN clause

Using the LIMIT clause in query

Related posts: