Second highest salary with a case expression
I’m doing some leetcoding and came across a SQL question to find the second highest salary. in cases where there isn’t a second highest salary, it should return null.
This solution:
select max(salary) SecondHighestSalary
from employee
where salary < (select max(salary) from employee)
will indeed return ‘null’ if there is no second salary, i.e. the max function will return null.
However, my question is for this query:
with sal as
(
select
salary,
rank() over (order by salary desc) sal_rank
from
employee
)
select
case
when salary is null
then null
else salary
end as SecondHighestSalary
from
sal
where
sal_rank = 2
Why does this return "" (ie blank) and not null like the max function?
