I solved this problem with user defined variables. No sort is needed, no tmp table needed.
This solution only do the table scan once and should be much faster if there is no index on the Salary column.
Note: seems only one SQL statement is permitted. So the init and unset of the variables need to be put in the union all.
- For each row, skip the row if current Salary equals @max;
- if current Salary > @max, then @secondMax = @max, @max = Salary;
- else if current Salary > @secondMax, then @secondMax = Salary;
select 1 from dual where (@max := -1) = -1 and (@secondMax := -1) <> -1 union all select Salary from Employee where if(@max = Salary, 0, 1) && if(@max < Salary, least(@secondMax := @max, @max := Salary) < -1, 1) && (@secondMax := greatest(@secondMax, Salary)) < -1 union all select if(@secondMax > -1, @secondMax, null) union all select 1 from dual where (@max := null) is null and (@secondMax := null) is not null;