SELECT IFNULL( (SELECT distinct Salary as SecondHighestSalary FROM Employee order by Salary desc limit 1,1) ,null);
No need to order all rows. The following is sufficient:
select max(Salary) from Employee where Salary < (select max(Salary) from Employee)
we can also solve it without IFNULL:
SELECT (SELECT distinct Salary as SecondHighestSalary FROM Employee order by Salary desc limit 1,1)
Well, actually you can use this one, a little bit faster:
SELECT * FROM ( SELECT `Salary` FROM `Employee` WHERE `Salary` != (SELECT MAX(`Salary`) FROM `Employee`) UNION SELECT NULL ) AS `SecondHighestSalary` ORDER BY `Salary` DESC LIMIT 1;
But the explain query looks bad.
it's no doubt good answer for especially this question, but the interviewer will surely ask you how to find the n-th highest salary. In this case the answer of OP is IMHO better
However, when I submit it, it says "Runtime Error Message: Commands out of sync; you can't run this command now". May I ask why? Thanks!
Will MySQL order all rows if "LIMIT" is given? It seems not via this page: http://dev.mysql.com/doc/refman/5.0/en/limit-optimization.html
Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.