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);
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;
@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;