CODEWITHSUNDEEP
sqlpostgresql
The Beast
The Beast

Reputation: 133

Return NULL when no records found Postgres

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?

Upvotes: 4

Views: 7088

Answers (4)

Parikshit Shinge
Parikshit Shinge

Reputation: 35

This should work:

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

Upvotes: 3

shawnt00
shawnt00

Reputation: 17915

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.

Upvotes: 2

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656291

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:

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:

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.

Upvotes: 6

Schwern
Schwern

Reputation: 164659

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.

Upvotes: 2

Related Questions