In the previous post we see how we can find the highest salary in MySQL. Here we will see how we can find the second highest salary in MySQL.
We have the following Salary table.
select * from salary;
+-------+--------+
| EmpId | salary |
+-------+--------+
| 101 | 1000 |
| 102 | 5000 |
| 103 | 2000 |
| 104 | 1000 |
| 105 | 9000 |
| 106 | 12000 |
| 107 | 10000 |
| 108 | 6000 |
| 109 | 25000 |
| 110 | 4000 |
+-------+--------+
10 rows in set (0.00 sec)In the Salary table, EmpId=109 has the highest salary and EmpId=106 has the second highest salary which is 12000. so we have to get the 2nd highest salary which is 12000.
There are so many ways to get the 2nd highest salary. Below MySQL query will be used to get the 2nd highest salary from the Salary table.
select Empid,salary from salary where salary!=(select max(salary) from salary) order by salary desc limit 1;
When we will execute the above query it will give us 2nd highest salary which is 12000.
select Empid,salary from salary where salary!=(select max(salary) from salary) order by salary desc limit 1;
+-------+--------+
| Empid | salary |
+-------+--------+
| 106 | 12000 |
+-------+--------+
1 row in set (0.04 sec)