SQL implementation LeetCode (176. Second highest salary)

[LeetCode] 176. Second Highest Salary

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

+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+

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

This question asks us to find the second largest number in a column of a table. There are many ways to solve this problem. Let's first look at a solution using the two keywords Limit and Offset. The number after Limit in MySQL limits the number of data we return, and Offset is the offset. So if we want to find the second highest salary, we can first sort the salaries in descending order, and then we set Offset to 1, which means starting from the second one, that is, the second highest salary, and then we set Limit to 1, which means only taking out the second highest salary. If Limit is set to 2, then both the second and third highest salaries will be taken out:

Solution 1:

SELECT Salary FROM Employee GROUP BY Salary
UNION ALL (SELECT NULL AS Salary)
ORDER BY Salary DESC LIMIT 1 OFFSET 1;

We can also use the Max function, which returns the maximum value. The logic is that we take out the maximum value of the numbers that do not contain the maximum value, which is the second largest value:

Solution 2:

SELECT MAX(Salary) FROM Employee 
WHERE Salary NOT IN
(SELECT MAX(Salary) FROM Employee);

The following method is basically the same as above, except that the less than sign < is used instead of the Not in keyword, and the effect is the same:

Solution 3:

SELECT MAX(Salary) FROM Employee
Where Salary <
(SELECT MAX(Salary) FROM Employee);

Finally, let's look at a method that can be extended to find the Nth highest salary. Just change the 1 in the following statement to N-1. The second highest salary is 1 when N-1 is substituted. The logic of the following statement is that if we want to find the second highest salary, then we allow one of the maximum values ​​to exist, and then find the largest value among the remaining numbers, which is the second largest value of the whole.

Solution 4:

SELECT MAX(Salary) FROM Employee E1
WHERE 1 =
(SELECT COUNT(DISTINCT(E2.Salary)) FROM Employee E2
WHERE E2.Salary > E1.Salary);

References:

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

This is the end of this article about SQL implementation of LeetCode (176. The second highest salary). For more relevant SQL implementation of the second highest salary content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!