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                 |
+---------------------+

1. 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;