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;




Related Posts

Nth Highest Salary Problem

LeetCode 177. Write a SQL query to get the nth...

Rank Scores Problem

LeetCode 178. Write a SQL query to rank scores. If...

Consecutive Numbers Problem

LeetCode 180. Write an SQL query to find all numbers...

Second Highest Salary Problem

LeetCode 176. Given the Employee table, write a SQL query...

Employees Earning More Than Their Managers Problem

LeetCode 181. Given the Employee table, write a SQL query...

Combine Two Tables Problem

LeetCode 175. Write a SQL query for a report that...