Get N-th Highest Salary Using DENSE_RANK() in MySQL
Intuition\nTo retrieve the N-th highest salary from the Employee table, we need to rank all distinct salaries in descending order. By using the DENSE_RANK() fun
# Intuition\nTo retrieve the N-th highest salary from the Employee table, we need to rank all distinct salaries in descending order. By using the `DENSE_RANK()` function, we can assign ranks to these salaries. The rank will help identify the N-th highest salary, which can then be selected from the ranked results. \n# Approach\nWe use a subquery to rank the salaries using `DENSE_RANK()` in descending order. This function assigns ranks to salaries such that salaries with the same value receive the same rank. The outer query then filters for the salary where the rank matches the desired value `N`, and `LIMIT 1` ensures that only a single result is returned.\n\n# Complexity\n- Time complexity: $$O(n \\log n)$$ due to the sorting required by the `DENSE_RANK()` function, where `n` is the number of rows in the Employee table.\n\n- Space complexity: $$O(n)$$, where `n` is the number of distinct salaries in the Employee table, as it stores the ranked salaries temporarily in memory.\n\n# Code\n```\n# Write your MySQL query statement below.\nCREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT\nBEGIN\n  RETURN (\n    SELECT Salary \n    FROM (\n      SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS ranking\n      FROM Employee\n    ) AS ranked_salaries\n    WHERE ranking = N\n    LIMIT 1\n  );\nEND;\n```
['Database', 'MySQL']