Leetcode 177. Nth Highest Salary

177. Nth highest Salary total accepted:10762 total submissions:66455 difficulty:medium

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 nth highest salary where n = 2 is 200. If there is no nth highest salary, then the query should return NULL.

Ideas:

This topic can be used in the previous topic, and do not need to rename the column name. The point to note is that the limit function supports only one number, not expressions, such as N-1. The last question is 2th, the code is limit 1, 1. This problem replaces the first 1 with N-1, but because the expression is not supported, you need to assign the value N = N-1 first. For the convenience of the reader, the explanation of the previous question is still attached to the end of the article.

CREATE FUNCTION getnthhighestsalary (N INT) RETURNS int
BEGIN
    set n=n-1;
  Return (
      # Write your MySQL query statement below.
      Select distinct Salary from Employee ORDER BY Salary desc limit N, 1
  );
End

When we use the query statement, often to return the first few or the middle of a few lines of data, this time how to do it. Don't worry, MySQL has provided us with such a feature.
SELECT * FROM table  LIMIT [offset,] rows | The rows offset offset

LIMIT clause can be used to force a SELECT statement to return the specified number of records. LIMIT accepts one or two numeric parameters. parameter must be an integer constant. Given two parameters, the first parameter specifies the offset of the first row to return the record, and the second parameter specifies the maximum number of rows to be returned. The offset of the initial record line is 0 (instead of 1): In order to be compatible with PostgreSQL, MySQL also supports syntax: LIMIT # offset #. 

mysql> SELECT * from table LIMIT 5,10;  Retrieving record rows 6-15

//To retrieve all row rows from an offset to the end of a recordset, you can specify a second parameter of-1: 
mysql> SELECT * from table LIMIT 95,-1;//retrieve record rows 96-las T.

If only one argument is given, it represents the maximum number of rows returned: 
mysql> SELECT * from table LIMIT 5;     Retrieves the first 5 rows

of records//In other words, LIMIT n is equivalent to LIMIT 0,n.