Query doesn't work.


  • 0
    L

    Tested on Oracle and it works fine. What do I need to change for mysql.

    select distinct(Salary) from Employees a where 2 = (select count(distinct(Salary)) from Employees b where b.Salary >=a.Salary);
    

  • 0
    L

    I figured it out today. I learned about LIMIT that works like rownum. So you would union the previous resultset with null and then pick the first one. Here it is which worked:

    select a.Salary as SecondHighestSalary from Employee a where 2 = (select count(distinct salary) from Employee b where b.salary >= a.salary) UNION select null limit 1;
    

  • 0
    E

    @labs Thank you for your brilliant solution. I tried to use limit also but does not work. Could you help me on this, why mine doesn't work?

    select a.Salary as SecondHighestSalary 
    from Employee a 
    order by a.Salary desc
    limit 1 offset 1
    
    UNION 
    
    select null 
    limit 1;
    

Log in to reply
 

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