LIMIT and OFFSET

LIMIT and OFFSET

The LIMIT and OFFSET clauses allow you to remove only a subset of the data rows from the query results:

SELECT select_list from
    table_expression
    [ORDER by Sort_expression1 [ASC | DESC] [, Sort_expression2 [ASC | DESC] ...]
    [LIMIT {number | All}] [OFFSET number]

If you give a LIMIT count, you will return rows that do not exceed that number (or perhaps less, because the query itself may generate fewer total rows). The LIMIT all and the omitted LIMIT clause are the same.

OFFSET indicates how many rows are ignored before starting the return row. Offset 0 is the same as omitting the offset clause. If both offset and LIMIT appear, the number of rows specified by LIMIT is ignored before the offset is calculated.

It is a good idea to use the LIMIT to constrain the result row into a unique order. Otherwise you'll be able to get to an unexpected subset. What you want is probably line tenth to 20th, but in what order 10 to 20. Unless you declare an order BY, the sequence is unknown.

The query optimizer takes LIMIT into account when generating query planning, so if you give LIMIT and OFFSET values differently, you are likely to get different plans (resulting in different row orders). Therefore, using different limit/offset values to select a different subset will produce inconsistent results unless you force an unexpected order with the orders by. This is not a bug, but a natural result, because SQL did not promise to send the results of the query in any particular order unless the order was used to constrain the sequence.

The rows ignored by the OFFSET clause still need to be evaluated inside the server; therefore, a large offset may still be inefficient.

eg

SELECT keyword from ' keyword_rank ' WHERE advertiserid= ' 59 '

ORDER BY keyword LIMIT 2 OFFSET 1;

For example, this SQL, limit followed by 2 data, offset is the 1th from the beginning of reading

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SELECT keyword from ' keyword_rank ' WHERE advertiserid= ' 59 '

ORDER BY keyword LIMIT 2, 1;

and this sql,limit is followed by the 2nd read, read 1 messages.