Get N-th Highest Salary Using DENSE_RANK() in MS SQL Server
Intuition\nTo retrieve the N-th highest salary from the Employee table, we rank all distinct salaries in descending order. By using the DENSE_RANK() function, w
# Intuition\nTo retrieve the N-th highest salary from the Employee table, we rank all distinct salaries in descending order. By using the `DENSE_RANK()` function, we assign ranks to these salaries. This ranking helps us 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 in descending order with `DENSE_RANK()`. This function assigns ranks to salaries, where salaries with the same value receive the same rank. The outer query filters for the salary where the rank matches the desired value `@N`. Unlike MySQL, T-SQL does not use `LIMIT` but handles single-row results differently.\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 T-SQL query statement below. */\nCREATE FUNCTION getNthHighestSalary (@N INT)\nRETURNS INT\nAS\nBEGIN\n  DECLARE @result INT;\n\n  SELECT @result = 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\n  RETURN @result;\nEND;\n```
['Database', 'MS SQL Server']