Two Simple Solutions for the Nth Highest Salary


  • 0
    G

    Method 1: LIMIT n, m or LIMIT m OFFSET n

    CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
    BEGIN
    declare M int
    set M = N-1
      RETURN (
          SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT M, 1
      );
    END
    

    Method 2: SUBQUERY, DISTINCT,

    CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
    BEGIN
      RETURN (
           select distinct e1.salary from Employee e1 where N-1 = (select count(distinct e2.Salary) from Employee e2 where e1.Salary < e2.Salary)
      );
    END
    

    TIPS:

    • distinct is a must, in case multiple people have the same amount of salary, the result have to be just salary and distinct.
    • MySQL provides a LIMIT clause that is used to specify the number of records to return. Please refer to LIMIT.

Log in to reply
 

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