Solve the problem with user defined variables

  • 0

    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;

    AC code:

    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;

Log in to reply

Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.