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?

The linked exercise from leetcode doesn't give a proper table definition. It matters whether salary can be NULL. Given that they ask for a NULL result, that's quite an omission. — Erwin Brandstetter
– Erwin Brandstetter, Commented Jan 6, 2022 at 4:33

Erwin Brandstetter · Accepted Answer · 2022-01-06 04:41:21Z

7

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.

edited Jan 6, 2022 at 4:41

answered Jan 6, 2022 at 4:28

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Parikshit Shinge · Accepted Answer · 2023-04-25 02:20:21Z

5

This should work:

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

answered Apr 25, 2023 at 2:20

Parikshit Shinge

552 silver badges4 bronze badges

1 Comment

Community Over a year ago

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.

Schwern · Accepted Answer · 2022-01-06 01:59:37Z

2

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.

edited Jan 6, 2022 at 1:59

answered Jan 6, 2022 at 1:49

Schwern

167k28 gold badges225 silver badges370 bronze badges

Comments

shawnt00 · Accepted Answer · 2022-01-06 07:09:34Z

2

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.

edited Jan 6, 2022 at 7:09

answered Jan 6, 2022 at 4:52

shawnt00

18.1k3 gold badges23 silver badges25 bronze badges

Collectives™ on Stack Overflow

Return NULL when no records found Postgres

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related