Accepted Solution of Nth Highest Salary


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

  • 0
    V

    Can you explain the syntax for M, 1 ?


  • 0
    A

    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).
    With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):


  • 0
    V

    Thanks for the explanation !!


  • 0
    P

    The IFNULL statement is not necessary actually.


  • 0
    S

    One of the test cases expects Salary to be null.

    max(Salary) can also be used in place of IFNULL.


  • 0
    H

    Why do we need DISTINCT keyword here?


  • 0
    R

    It's not clear why you had to use another variable M and set to N-1, rather than just using N-1 directly in the limit clause. I know that causes an error, but why exactly?


Log in to reply
 

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