Leetcode_Database-176. Second Highest Salary

#Leetcode Database

176. Second Highest Salary  

URL: https://leetcode.com/problems/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 query should return 200 as the second highest salary. If there is no second highest salary, then the query should return null.

+---------------------+
| SecondHighestSalary |
+---------------------+
| 200                 |
+---------------------+

 

Solution:

Select (Select DISTINCT Salary From Employee order by Salary desc limit 1 Offset 1)  AS  SecondHighestSalary

 

【Note】：

limit n1 offset n2 .的使用: 从第n2行开始（不包含n2）取n1行数据

eg:

Select * from student limit 3 offset 10

从第10行开始往下去3行，即 11,12,13行的数据

 

原本想使用 Rank() Over(） 但是提交到时候一个case不通过，就是当如表中只有一行数据的时候，使用my_rank=2则取不到数据。

遂放弃了这个方法

# SELECT Salary SecondHighestSalary 
# FROM
# (SELECT Salary, RANK() OVER ( ORDER BY Salary Desc) my_rank FROM  Employee)  AS tmp
#  WHERE my_rank = 2