Return NULL when no records found Postgres

Question

I am trying to solve Leetcode's second highest salary (https://leetcode.com/problems/second-highest-salary/). Here's what I implemented on postgres:

select foo.salary as "SecondHighestSalary"
from 
(select salary,
dense_rank() over (order by salary desc) as rank
from Employee) foo
where rank = 2;

But the issue is, I need to return NULL if there are no records with rank = 2. I then tried

select (case
    when count(1) = 0 then NULL
    else salary
    end
)
from 
(select salary,
dense_rank() over (order by salary desc) as rank
from Employee) foo
where rank = 2
group by salary;

But it still returns no records. How do I output NULL when no records are returned?

Answer 1

You don't actually need COALESCE, just an outer SELECT:

SELECT (
   SELECT salary FROM (
      SELECT salary, dense_rank() OVER (ORDER BY salary DESC NULLS LAST) AS rank
      FROM   employee
      ) sub
   WHERE  rank = 2
   LIMIT  1
   ) AS second_highest_salary;

See:

• Return a value if no record is found

Be sure to add NULLS LAST if salary can be NULL, or you are in for a surprise. (You'd get the highest salary.) See:

• Sort NULL values to the end of a table

And there can be multiple rows with rank = 2, so add LIMIT 1.

With an index on salary, Schwern's 2nd query will be substantially faster, though - while dodging the NULL issue because max() excludes NULL values, and dodging the "no row" issue because aggregate functions always return a row, defaulting to NULL in absence of a value.

Answer 2

This should work:

SELECT 
  (SELECT DISTINCT salary
   FROM Employee
   ORDER BY salary DESC
   LIMIT 1 OFFSET 1) as SecondHighestSalary