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?

CodePudding user response：

I think you are complicating a little.

Try :

SELECT MAX(Salary) as Salary
From Employee1 
WHERE Salary < ( SELECT Max(Salary) FROM Employee1);

Demo

CodePudding user response：

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

CodePudding user response：

If you need to always return a row with NULL if there's no qualifying second-highest value you need to outer join to a source row - this will allow you to return all columns if required.

select Salary
from
(select 2 as s) choice
left join (
  select salary, dense_rank() over(order by salary desc) rnk
  from Employee
)s on  s.rnk = s;

Example Fiddle