No Variable, No Limit X,1, Just one query, 808ms


  • 22
    J
        CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
    BEGIN
        
      RETURN (
          # Write your MySQL query statement below.
          
        
          SELECT e1.Salary
          FROM (SELECT DISTINCT Salary FROM Employee) e1
          WHERE (SELECT COUNT(*) FROM (SELECT DISTINCT Salary FROM Employee) e2 WHERE e2.Salary > e1.Salary) = N - 1      
          
          LIMIT 1
          
          
          
          
      );
    END

  • 0
    C

    good solution, but "LIMIT 1" seems to be redundant


  • 0
    H

    removing limit 1 slows down the query, I wonder why this is?


  • 0
    H

    also, why is this faster than a correlated subquery? this is a very interesting query


  • 0
    J

    Probably because of the query optimization: the engine basically runs three very similar/same queries, many meta-data can be reused.


  • 0
    Y

    why use DISTINCT for the two sets? Will it change the ranking when redundant added?


  • 1
    B

    What if there're same Salaries? Say, there are N-2 salaries smaller than the n th, and (n-1)th=n th, then there'll never be COUNT(*) = N-1, right?


  • 1
    V

    @chriswho when I remove limit 1, it returns more than one row. So maybe there are more than one highest score we should only keep one result.

    By the way, this is my similarly solution:

    BEGIN
      RETURN (
    
          # Write your MySQL query statement below.
    
          select distinct Salary from Employee e 
          where N=(select count(distinct Salary) from Employee where Salary>=e.Salary)
          limit 1
    
      );
    END
    
    

  • 0
    C

    CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
    BEGIN
    RETURN (
    # Write your MySQL query statement below.
    SELECT e1.Salary
    FROM (SELECT DISTINCT Salary FROM Employee) e1
    WHERE N-1=(SELECT COUNT(DISTINCT e2.Id) FROM Employee e2 WHERE e2.Salary>e1.Salary)
    );
    END

    --

    This is a slight variation but I can't figure out why it doesn't work! I'm counting the number of distinct Id instead of counting number of rows. Why does this produce an error?


  • 0
    L

    @chriswho
    "LIMIT 1" can avoid "full table scan" on non-index column.


  • 0
    Y

    it's remarkable. thanks!


  • 0
    X
    This post is deleted!

Log in to reply
 

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