Skip to main content

LeetCode 177. Nth Highest Salary SQL Solution

Problem​

LeetCode SQL Problem

  1. Nth Highest Salary

Solution: Subquery​

  • In the WHERE statement, use a correlated subquery to check and include the current row if the current salary is the Nth highest salary we are looking for.
  • Then we use DISTINCT keyword to ensure we only show one output value as there might be multiple employees having the same Nth highest salary.
CREATE FUNCTION getNthHighestSalary
(
    N INT
)
RETURNS INT

BEGIN
    RETURN
    (
        SELECT DISTINCT
               (Salary) AS getNthHighestSalary
        FROM Employee e1
        WHERE N - 1 =
        (
            SELECT COUNT(DISTINCT salary)
            FROM employee e2
            WHERE e2.salary > e1.salary
        )
    );
END

Solution: DENSE_RANK() Window Function​

  • In the inner SELECT query, use Window function Dense_Rank() to rank employee salary from highest to lowest and ensure there is no gap in the ranking values.
  • In the outer SELECT query, we are using the WHERE statement to get the Nth highest salary and use DISTINCT keyword to show only one output value.
CREATE FUNCTION getNthHighestSalary
(
    N INT
)
RETURNS INT

BEGIN
    RETURN
    (
        SELECT DISTINCT
               (salary)
        FROM
        (
            SELECT salary,
                   DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank_num
            FROM Employee
        ) AS ranked_salary
        WHERE dense_rank_num = N
    );
END