177. Nth Highest Salary

Write a function returning the N-th highest distinct salary, or NULL if there are fewer than N distinct salaries.

OFFSET N - 1 skips the higher salaries. For N <= 0 a negative offset would be an error in PostgreSQL, so the CASE returns NULL instead. The wrapping subquery again gives NULL instead of no row.

Lesson: subqueries and CTEs.

Solutions

CREATE OR REPLACE FUNCTION NthHighestSalary(N INT) RETURNS TABLE (Salary INT) AS $$
BEGIN
  RETURN QUERY (
    SELECT CASE WHEN N > 0 THEN (
      SELECT DISTINCT e.salary FROM Employee e
      ORDER BY e.salary DESC
      LIMIT 1 OFFSET GREATEST(N - 1, 0)
    ) END
  );
END;
$$ LANGUAGE plpgsql;