[Leetcode] Nth highest Salary First n high-paying water

Write a SQL query to get the nth highest salary from the Employee table.

+----+--------+| Id | Salary |+----+--------+| 1 | |    | 2 | | |    3  |    |+----+--------+

For example, given the above Employee table, the n-th highest salary where n = 2 is 200 . If there is no nth highest salary and then the query should return null .

This problem is the extension of the previous second highest salary, according to the practice of the previous problem, we can easily push it to N, according to second highest salary in the analysis of solution one, We just need to change the back of offset 1 to N-1, but so MySQL will error, estimation does not support the operation, then we can add a set n = N-1, the n will first become N-1 again do the same:

Solution One:

CREATE FUNCTIONGetnthhighestsalary (NINT)RETURNS INTBEGIN  SETN=N- 1; RETURN (      SELECT DISTINCTSalary fromEmployeeGROUP  bySalaryORDER  bySalaryDESCLIMIT1OFFSET N);END

Based on the analysis of solution four in the second highest salary, we only need to change its 1 to N-1, here it supports the calculation of N-1, see Code as follows:

Solution Two:

CREATE FUNCTIONGetnthhighestsalary (NINT)RETURNS INTBEGIN  RETURN (      SELECT MAX(Salary) fromEmployee E1WHEREN- 1 =      (SELECT COUNT(DISTINCT(E2. Salary)) fromEmployee E2WHEREE2. Salary>E1. Salary));END

Of course we can also change the last > to >= so that we can change the N-1 to N:

Solution Three:

CREATE FUNCTIONGetnthhighestsalary (NINT)RETURNS INTBEGIN  RETURN (      SELECT MAX(Salary) fromEmployee E1WHEREN=      (SELECT COUNT(DISTINCT(E2. Salary)) fromEmployee E2WHEREE2. Salary>=E1. Salary));END

Similar topics:

Second Highest Salary

Resources:

Https://leetcode.com/discuss/88875/simple-answer-with-limit-and-offset

Https://leetcode.com/discuss/63183/fastest-solution-without-using-order-declaring-variables

Leetcode all in one topic summary (continuous update ...)

[Leetcode] Nth highest Salary First n high-paying water