Get N-th Highest Salary Using RANK() in Oracle
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 RANK() function,
# 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 `RANK()` function, we can assign ranks to these salaries. The rank will help us identify the N-th highest salary, which can then be selected from the ranked results.\n\n# Approach\nWe use a subquery to rank the distinct salaries in descending order with `RANK()`. This function assigns ranks such that salaries with the same value receive the same rank. The outer query then filters to get the salary where the rank matches the desired value `N`.\n\n# Complexity\n- Time complexity: $$O(n \\log n)$$ due to the sorting required by the `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 PL/SQL query statement below */\nCREATE FUNCTION getNthHighestSalary(N IN NUMBER) RETURN NUMBER IS\n    result NUMBER;\nBEGIN\n    SELECT salary INTO result\n    FROM (\n        SELECT DISTINCT salary,\n                        RANK() OVER (ORDER BY salary DESC) AS ranking\n        FROM Employee\n        GROUP BY salary\n    ) ranked_salaries\n    WHERE ranking = N;\n    RETURN result;\nEND;\n```
['Database', 'Oracle']