MySQL in a Nutshell, 2nd Edition

Name

LIMIT

Synopsis

...
LIMIT count |
LIMIT [offset,] count |
LIMIT count OFFSET offset

Use the LIMIT clause to limit the number of rows the server will process to satisfy the given SQL statement. For the SELECT statement, it limits the number of rows returned in the results set. In an UPDATE statement, it limits the number of rows changed. With the DELETE statement, it limits the number of rows deleted. The DELETE statement permits only the first syntax shown, whereas the other statements allow all three.

The LIMIT clause accepts only literal values, not expressions or variables. Nor will it accept a negative value. The most straightforward method of limiting the number of rows is to specify the maximum row count to be displayed, like this:

SELECT * FROM employees
LIMIT 5;

To begin listing rows after a specific number of records, an offset may be given, where the offset for the first row is 0. Two syntaxes accomplish this. One gives the amount of the offset, followed by a comma and then the maximum count of rows to display. The other specifies the count followed by the OFFSET keyword, followed by the amount of the offset. Here is an example of the first structure, which is preferred:

SELECT * FROM employees
LIMIT 10, 5;

In this example, after the 10th record is reached, the next 5 records will be returned—in other words, results 11 through 15 are returned. The offset and count for the LIMIT clause are based on the rows in the results set, not necessarily on the rows in the tables. So the amount of the offset is related to the order of the rows retrieved from the tables based on clauses, such as the WHERE clause and the ORDER BY clause.

Table of Contents for MySQL in a Nutshell, 2nd Edition

Name

Synopsis

Table of Contents for
MySQL in a Nutshell, 2nd Edition