SQL Leetcode: 176. Second Highest Salary
176. Second Highest Salary
Easy
1083535Add to ListShare
SQL Schema
Write a SQL query to get the second highest salary from the Employee
table.
+----+--------+
| 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
.
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200 |
+---------------------+
- Using Window Rank function
SELECT
MAX(CASE
WHEN SALARY IS NOT NULL THEN SALARY
ELSE null
END ) AS SecondHighestSalary
FROM (
SELECT
Salary,
dense_rank() OVER (ORDER BY SALARY DESC) as rank_salary
FROM
Employee
) rank_table
WHERE rank_salary = 2
2. Using correlated subquery ( Top-Down Approach)
SELECT
Salary as SecondHighestSalary
FROM EMPLOYEE e1
WHERE 2–1 = (SELECT COUNT(DISTINCT SALARY) FROM Employee e2
WHERE e2.salary > e1.salary )
UNION
(SELECT null)
LIMIT 1;