A solution based on problem 178 "rank scores", 913ms


  • 0
    S
    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

  • 0
    M

    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
    

Log in to reply
 

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