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?

You May Also Like

More From Author