[SQL] 리트코드 176. Second Highest Salary

문제

Table: Employee

+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| salary      | int  |
+-------------+------+

id is the primary key (column with unique values) for this table.
Each row of this table contains information about the salary of an employee.
 
Write a solution to find the second highest salary from the Employee table.

If there is no second highest salary, return null (return None in Pandas).
The result format is in the following example.

 

https://leetcode.com/problems/second-highest-salary/description/

LeetCode - The World's Leading Online Programming Learning Platform

예시

Example 1:
Input: 
Employee table:

+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

Output: 

+---------------------+
| SecondHighestSalary |
+---------------------+
| 200                 |
+---------------------+

Example 2:
Input: 
Employee table:

+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
+----+--------+

Output: 

+---------------------+
| SecondHighestSalary |
+---------------------+
| null                |
+---------------------+

문제 풀이

SELECT IFNULL(
    (SELECT DISTINCT salary
     FROM Employee
     ORDER BY Salary DESC
     LIMIT 1 OFFSET 1), NULL) AS SecondHighestSalary

* IFNULL(expr1, expr2)

>> expr1의 결과가 NULL이 아니면 expr1 반환

expr1의 결과가 NULL이면 expr2 반환

SELECT MAX(salary) AS SecondHighestSalary
FROM Employee
WHERE Salary < (SELECT MAX(Salary)
                     FROM Employee)

---

SQL을 독학하시는 분들에게 도움이 되길 바라며,

혹 더 좋은 방법이 있거나 오류가 있다면 편하게 말씀 부탁드립니다.