I don't know why my solution doesn't work.


  • 0
    N

    select if(Count(e.Salary) > 1, e.Salary, NULL) as SecondHighestSalary from Employee e
    order by e.Salary DESC limit 1,1;

    my answer is not working.

    It seems not to be correct this statement "if(Count(e.Salary) >1, e.Salary, NULL).

    but i don't know this cause.
    Could you say how to fix this problem?


  • 0
    X
    SELECT IF(COUNT(DISTINCT(e1.Salary)) > 1, MIN(e1.Salary), null) AS SecondHighestSalary
    FROM (SELECT Salary FROM Employee e ORDER by e.Salary DESC LIMIT 2) e1;
    

    By executing your code, MySQL would first count salaries in the the whole original column and do the ORDER BY and LIMIT functions next, in which case the outcome would definitely not satisfy the requirement. I have pasted a modified version based on your code that worked.

    The code above first create a new table e1, which has the highest two values or the highest value depending on how many entries in the original salary column. Note, you should use LIMIT 2 instead of LIMIT 1, 1 because there are cases where there could be only one entry in the original salary column. Then execute the if statement. Count how many distinct values contained in the new table created. If there are more than more one distinct values, then execute MIN function to find the second highest value, else return 'null'.

    Let me know if you have any questions.


Log in to reply
 

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