NTH HIGHEST SALARY


  • 2
    T

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


  • 0
    P

    CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
    BEGIN
    RETURN (
    # Write your MySQL query statement below.
    select
    IFNULL(
    (select a.salary
    from (select
    e.salary
    , @curRank := @curRank + 1 AS rank
    from (select distinct salary from Employee order by salary desc limit N) e, (SELECT @curRank := 0) r) a
    where a.rank=N), NULL)
    );
    END


  • 0
    A
    CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
    BEGIN
      RETURN (
          # Write your MySQL query statement below.
          
          select distinct salary AS getNthHighestSalary
           from employee Emp1
                WHERE (N-1) = ( 
                     SELECT COUNT(DISTINCT(Emp2.Salary))
                            FROM Employee Emp2
                            WHERE Emp2.Salary > Emp1.Salary)
          
          
      );
    END 
    

  • 0
    R

    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 1 offset M), null ));
    END


  • 0
    S

    The following solution is better and would perform better.

    CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
    BEGIN
    RETURN (
    SELECT
    IFNULL(
    (SELECT DISTINCT Salary
    FROM Employee as e1
    where N =(select count(distinct(salary)) from Employee as e2 where e2.salary >= e1.salary )),
    NULL) AS SecondHighestSalary
    );
    END


  • 0
    C
    CREATE FUNCTION getNthHighestSalary(@N INT) RETURNS INT AS
    BEGIN
        RETURN (
            SELECT Max(Salary) from Employee where Salary not in (Select DISTINCT TOP (@N-1) Salary from Employee order by Salary Desc)
            
        );
    END
    

Log in to reply
 

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