Accpted Solution for the Nth Highest Salary


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

  • 6
    L

    Why it is syntax error when delete the declare part and insert N - 1 into query statement?


  • 0

  • 8
    D

    @rekinyz The Answer you provided is right. In addition, I would like to provide a solution using TOP and Limit and it's with subquery but leetcode's compiler does not support it.

    Select TOP 1 Salary from Employee where Salary in ( SELECT TOP N DISTINCT Salary FROM Employee ORDER BY Salary DESC )

    OR

    Select Salary from Employee where Salary in ( SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT N) LIMIT 1


  • 0
    L

    I submit like this:

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

    why not pass?


  • 0
    S

    Good question, I've the same doubt, why not pass? Can any guru shed some light please?
    Thanks


  • 0
    B

    Thanks for the alternate approach. Can i know whey do we need LIMIT 1 at the end? Does LIMIT N already not give us top N rows?


  • 0
    D

    You need a return


  • -2
    K

    This query uses filesort and temptables. Not the best one.


  • 0
    L

    In "Second Highest Salary", why this SQL not pass :
    SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 1, 1


  • 0
    S

    You need a return null, Use UNION SELECT


  • 0
    N

    **

    when tring your solution, why it shows me : " SyntaxError: near '; " after I added delimiter $$ ,it do not work neither , WHY?

    **


  • 1
    L

    Why don't you need a return null?
    But question 176 "Second Highest Salary" need?


  • 0
    C

    it takes 896 ms.


  • 0
    P

    Can someone tell me what is wrong with my code? It works fine in my local machine

    CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
    BEGIN
    RETURN (
    # Write your MySQL query statement below.
    select IFNULL((Select E.Salary from Employee E where (N-1)=(select count(Distinct m.Salary) from Employee m where m.Salary>E.Salary)), NULL)
    );
    END

    while running in here throws a message "Subquery returns more than 1 row"
    {"headers": {"Employee": ["Id"
    "Salary"]}
    "argument": 1
    "rows": {"Employee": [[1
    100]
    [2
    100]]}}


  • 1
    C

    @lantian_bupt select IFNULL((SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 1, 1),null) as SecondHighestSalary


  • 0
    CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
    BEGIN
    SET N = N - 1; 
      RETURN (
          SELECT DISTINCT(Salary) FROM Employee
          Union SELECT null 
          ORDER BY Salary DESC LIMIT 1 OFFSET N
      );
    END

  • 0
    T

    This one fail on test 13/14. anyone know why?

    select  Salary from Employee e where (select count(*) from Employee f where e.id != f.id and e.salary < f.salary) = N-1 limit 1
    

  • 0
    S

    @rekinyz
    Please help me understand
    Why should we use distinct here?
    how it is different from
    "select (select salary from employees order by salary desc limit 1 offset M)"
    where M=N-1;

    This query can only pass 11/14 test cases.


  • 0
    A
    This post is deleted!

Log in to reply
 

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