Dynamic Nth Highest Salary Function in SQL

Question

Write a SQL query to get the Nth highest distinct salary from the `employee` table. If there are fewer than N distinct salaries, return `NULL`.

Accepted Answer

Optimal Solution:
CREATE OR REPLACE FUNCTION getNthHighestSalary(N INT) 
RETURNS TABLE (salary INT) AS $$
BEGIN
  RETURN QUERY (
    WITH ranked_salaries AS (
      SELECT 
        e.salary,
        DENSE_RANK() OVER (ORDER BY e.salary DESC) AS rnk
      FROM employee e
    )
    SELECT DISTINCT r.salary
    FROM ranked_salaries r
    WHERE r.rnk = N
  );
END;
$$ LANGUAGE plpgsql;

Technical Explanation:
`DENSE_RANK()` assigns identical ranks to tied salaries without skipping rank numbers. Filtering `rnk = N` isolates the exact Nth distinct salary tier.

id	salary
1	100
2	200
3	300