Second Highest Salary Problem
Description
LeetCode Problem 176.
Write a SQL query to get the second highest salary from the Employee table.
1
2
3
4
5
6
7
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
For example, given the above Employee table, the query should return 200 as the second highest salary. If there is no second highest salary, then the query should return null.
1
2
3
4
5
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200 |
+---------------------+
MySQL Solution
We can sort the distinct salary in descending order. Then we can utilize the limit clause to get the second highest salary. This is the inner select clause.
However, we need to consider the case when there is only one record in the table. The outer select clause is to take the select result as a table to avoid this issue.
1
2
3
4
5
6
select
(select distinct Salary
from Employee
order by Salary desc
limit 1 offset 1)
as SecondHighestSalary;
LeetCode Database - Easy
LeetCode 175
LeetCode 176
LeetCode 181
LeetCode 182
LeetCode 183
LeetCode 196
LeetCode 197
LeetCode 511
LeetCode 512
LeetCode 577
LeetCode 584
LeetCode 586
LeetCode 595
LeetCode 596
LeetCode 597
LeetCode 603
LeetCode 607
LeetCode 610
LeetCode 613
LeetCode 619
LeetCode 620
LeetCode 627
More LeetCode Database
MySQL Tutorials