Why using LIMIT N-1,1 will cause error?


  • 13
    S
    CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
    BEGIN
    RETURN (
          # Write your MySQL query statement below.
          SELECT IFNULL((SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT N-1 ,1), NULL)  
      );
    END
    

    This was my code for the problem, but it seems there's an error near LIMIT N-1,1. Anyone has any idea why?


  • 20

    Seems like MySQL can only take numeric constants in the LIMIT syntax. Directly from MySQL documentation:

    The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants (except when using prepared statements).


  • 5
    K

    My accepted solution by LIMIT.

    CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
    BEGIN
        set N=N-1;
      RETURN (
          # Write your MySQL query statement below.
          select ifnull((select distinct Salary from Employee order by Salary desc limit N,1),null)
      );
    END

  • 1
    X

    ifnull seems to be redundant


  • 0
    B

    @xiuyi I am afraid that ifnull is not redunant. Because the problem tell us "If there is no nth highest salary, then the query should return null". Without ifnull, the query wouldn't return null as expected.


Log in to reply
 

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