[Leetcode] Second highest Salary second high-paying water

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

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

For example, given the above Employee table, the second highest salary is 200 . If there is no second highest salary and then the query should return null .

This problem let us find the table in a column of the second largest number, this problem has a number of solutions, first of all, the use of limit and offset two keyword solution, mysql in the back of the limit of the number of data, offset is offset, then if we want to find a second high-paying water, We can first sort the salary in descending order, then we set the offset to 1, then we start from the second, that is, the second high-paying water, and then we will limit to 1, that is only to take out the second-highest-paid water, if limit is set to 2, then the second and third higher-paid water are taken out:

Solution One:

SELECT  from GROUP  by Salary UNION  All (SELECTNULL as Salary) ORDER  by DESC 1 1;

We can also use the Max function, which returns the maximum value, which is the maximum value in the number that we take out that does not contain the maximum value, which is the second largest value:

Solution Two:

SELECT MAX  from  WHEREnot in(SELECTMAX from Employee);

The following method is basically the same as above, that is, with the less than sign < instead of the not in keyword, the effect is the same:

Solution Three:

SELECT MAX  from Employee Where < (SELECTMAX from Employee);

Finally, a method can be extended to find the nth high salary, as long as the following statements in the 1 to N-1 can be, the second high salary into N-1 is 1, the logic of the following statement is, if we want to find a second high salary, then we allow one of the largest value, and then in the remaining numbers to find the largest, That is, the second largest value for the whole;

Solution Four:

SELECT MAX  from Employee E1 WHERE 1 = (SELECTCOUNT(DISTINCT from Employee E2WHERE  > E1. Salary);

Resources:

Https://leetcode.com/discuss/47041/very-very-simple-solution

Https://leetcode.com/discuss/42849/general-solution-not-using-max

Https://leetcode.com/discuss/21751/simple-query-which-handles-the-null-situation

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

[Leetcode] Second highest Salary second high-paying water