SQL CASE WHEN won’t meet condition

select salary as SecondHighestSalary
from Employee
where salary = case 
    when (select count(*)
         from Employee) <= 1 then null
    when (
        select count(*)
        from Employee
    ) > 1 then (
        select salary
        from (select salary
              from Employee
              order by salary desc
              limit 2
        ) as two_highest_salary_table
        order by salary asc
        limit 1
    )
    end;

This is a solution to one of the leetcode problem. It is asking me to output the second highest salary from the table and if there are no second highest salary then the output should be null.

The above is my solution. I used case and when syntax but the problem is that even when the table only has 1 row, it doesn’t output a table with a NULL value but it just output a table w nothing in it.

How can I fix this problem?

Answer

It is much simpler, as you thought, a simole SELECT with LOT and OFFSET is enough

CREATE TABLE Employee (salary DECIMAL(10,2))

INSERT INTO Employee VALUES(10000.1)

select salary
              from Employee
              order by salary desc
              limit 1,2

| salary |
| -----: |

INSERT INTO Employee VALUES(20000.2)

select salary
              from Employee
              order by salary desc
              limit 1,2

|   salary |
| -------: |
| 10000.10 |

db<>fiddle here