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?

CodePudding user response：

The solution doesn't work because there's no row for the case to act on.

You can use coalesce and a sub-select.

Rewriting it as a CTE makes the sub-select more compact.

with ranked_salaries as (
  select
    salary,
    dense_rank() over (order by salary desc) as "rank"
  from Employee
)
select
  coalesce(
    (select salary from ranked_salaries where "rank" = 2),
    null
  );

Note that this is a simpler and faster approach for this particular problem.

select max(salary)
from Employee
where salary < (select max(salary) from Employee)

If salary is indexed, this should be very fast.

CodePudding user response：

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.

CodePudding user response：

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

Using a dummy aggregate for the output will guarantee a row is returned. It also deals with the potential for tying rows.