Write a query to find second highest salary inMySql . This question is mostly asked in an interviews and it’s also a bit tricky. In this tutorial, We are going to learn three easiest ways to write a MySql query to find second highest salary.
Find Second Highest Salary in MySql
Suppose we have one table Employee. In this table, we have following record of an employee and each employee has a different salary. As per table, the highest salary of an employee is 12000 and second highest salary is 10000. How to write a query to find second highest salary of an employee.
|
1 2 3 4 5 6 7 8 |
+--------+--------+-------------------+--------+ | emp_id | name | designation | salary | +--------+--------+-------------------+--------+ | 1 | John | Manager | 10000 | | 2 | smith | Sales Associate | 3000 | | 3 | Amit | Software Engineer | 12000 | | 4 | vikash | Customer Support | 9000 | +--------+--------+-------------------+--------+ |
Difference between inner join and left join
Let’s first write a MySql query to find the maximum salary. In MySql, we can use MAX() function to find the highest value of a selected column.
|
1 2 3 4 5 6 7 8 9 10 |
// Find highest salary select MAX(salary) from employee; +-------------+ | Max(salary) | +-------------+ | 12000 | +-------------+ 1 row in set (0.00 sec) |
How to Find Second Highest Salary in Mysql
If we look at the employee table we know emp_id 1 is getting a second highest salary. Now lets’s write a MySql query to find second highest salary.
First Method : Using sub-query
|
1 2 3 4 5 6 7 8 9 10 |
// Second highest salary select salary from employee where salary not in (select MAX(salary) from employee) order by salary desc limit 1; +--------+ | salary | +--------+ | 10000 | +--------+ 1 row in set (0.00 sec) |
In this approach, we have used sub-query to find max salary first then excluded this value using not in to find second highest value.
Second Method – Using Limit
Using limit to find the second highest salary. In this query, we are using a Distinct keyword to filter duplicate records.
|
1 2 3 4 5 6 7 |
select distinct salary from employee order by salary desc limit 1,1; +--------+ | salary | +--------+ | 10000 | +--------+ |
Third Method to find second highest salary in Mysql
|
1 2 3 4 5 6 7 8 9 10 11 12 |
select max(salary) from employee where salary not in(select max(salary) from employee); or select max(salary) from employee where salary < (select max(salary) from employee); +-------------+ | max(salary) | +-------------+ | 10000 | +-------------+ 1 row in set (0.00 sec) |
Conclusion
In this post, I have used some common approaches to find second highest salary in MySql. If you know some other ways to find second highest salary you can let us know through your comments.
