Based on Count and easy to extend to N


  • 8
    R
     SELECT MAX(Salary)
     FROM (SELECT E1.Salary
     FROM Employee as E1 JOIN Employee as E2
     ON E1.Salary < E2.Salary
     GROUP BY E1.Id HAVING COUNT(E2.Id) = 1
     )  AS SecondHighestSalary
     ORDER BY Salary DESC LIMIT 1;
    

    For the Nth highest one,

     COUNT(E2.Id) = N-1

  • 0
    S

    Hi, could you please explain this part "SELECT E1.Salary
    FROM Employee as E1 JOIN Employee as E2
    ON E1.Salary < E2.Salary
    GROUP BY E1.Id HAVING COUNT(E2.Id) = 1"!

    Thanks!


  • 0
    W

    @subbrammanian

    It means each time we find all Ids in E2 which Salary are larger than ones in E1.Then we calculate how many Ids in E2 satisfied this rule.

    If there is only one, it means it is the second one, so the only one larger than it is the first one.


  • 0
    N

    this works great for this example, but in case we have duplicate e.g. id = 4 and salary = 200. then your internal query count will be not equal to N-1


Log in to reply
 

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