CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT BEGIN RETURN ( select distinct Salary from (select Salary, ( select count(*)+1 from (select distinct (Salary) from Employee) as uniq where Salary > e.Salary) as Rank from Employee e ) as new # table 'new' has the salary rank where new.Rank = N ); END
Inspired by your method, I got a simple version which is much slower...
SELECT DISTINCT Salary FROM ( SELECT e.Salary, ( SELECT COUNT(DISTINCT d.Salary) FROM Employee d WHERE d.Salary >= e.Salary) Rank FROM Employee e ) new WHERE new.Rank = N
And another one. This one is better and it's similar to the original one.
SELECT DISTINCT Salary FROM Employee e WHERE ( SELECT COUNT(*) FROM (SELECT DISTINCT Salary FROM Employee) uniq WHERE Salary >= e.Salary) = N