Get N-th Highest Salary Using DENSE_RANK() in PostgreSQL
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 helps 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 salaries in descending order with `DENSE_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`, and `LIMIT 1` ensures that only one 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 PostgreSQL query statement below.\nCREATE OR REPLACE FUNCTION NthHighestSalary(N INT) \nRETURNS TABLE (Salary INT) AS $$\nBEGIN\n  RETURN QUERY (\n    SELECT a.salary\n    FROM (\n      SELECT e.salary, \n             DENSE_RANK() OVER (ORDER BY e.salary DESC) AS ranked\n      FROM Employee e\n    ) a\n    WHERE a.ranked = N\n    LIMIT 1\n  );\nEND;\n$$ LANGUAGE plpgsql;\n```
['Database', 'PostgreSQL']