LeetCode 176. Second Highest Salary SQL Solution

Problem

LeetCode SQL Problem

176. Second Highest Salary

Solution - Dense_Rank() Approach

• Use Window function Dense_Rank() to rank employee salary from highest to lowest.
• Select the second highest salary rank
• We have to return NULL if there is no second highest salary in the data set. The outer SELECT clause is to take the inner SELECT result as a table to achieve this.

MySQL

WITH salary_ranked
AS (
    SELECT salary
        , DENSE_RANK() OVER (
            ORDER BY salary DESC
            ) AS rank_num
    FROM employee
    )
SELECT (
        SELECT DISTINCT (salary_ranked.salary)
        FROM salary_ranked
        WHERE rank_num = 2
        ) AS SecondHighestSalary;

TSQL

WITH salary_ranked
AS (
    SELECT salary
        , DENSE_RANK() OVER (
            ORDER BY salary DESC
            ) AS rank_num
    FROM employee
    )
SELECT (
        SELECT DISTINCT (salary_ranked.salary)
        FROM salary_ranked
        WHERE rank_num = 2
        ) AS SecondHighestSalary;

Solution - Limit Offset Approach

• In the inner SELECT clause, sort the distinct salary in descending order.
• Utilize the LIMIT clause with offset to skip the top salary and get the second highest salary.
• However, we need to return NULL if there is no second highest salary. This can be achieved by wrapping inner SELECT with an outer SELECT.

MySQL

SELECT (
        SELECT DISTINCT salary
        FROM employee
        ORDER BY salary DESC LIMIT 1 offset 1
        ) AS SecondHighestSalary;