Limiting and Offsetting Results
Use the LIMIT
clause to limit the number of results returned from a SELECT statement. For example, if there are 1000 rows in the Users table, limit the number of rows to return by specifying a LIMIT value. For example, this statement returns the first four ID rows from the table:
sql-> SELECT * from Users ORDER BY id LIMIT 4;
+----+-----------+----------+-----+--------+
| id | firstname | lastname | age | income |
+----+-----------+----------+-----+--------+
| 1 | David | Morrison | 25 | 100000 |
| 2 | John | Anderson | 35 | 100000 |
| 3 | John | Morgan | 38 | NULL |
| 4 | Peter | Smith | 38 | 80000 |
+----+-----------+----------+-----+--------+
4 rows returned
To return only results 3 and 4 from the 10000 rows use the LIMIT
clause to indicate 2 values, and the OFFSET clause to specify where the offset begins (after the first two rows). For example:
sql-> SELECT * from Users ORDER BY id LIMIT 2 OFFSET 2;
+----+-----------+----------+-----+--------+
| id | firstname | lastname | age | income |
+----+-----------+----------+-----+--------+
| 3 | John | Morgan | 38 | NULL |
| 4 | Peter | Smith | 38 | 80000 |
+----+-----------+----------+-----+--------+
2 rows returned
Note:
We recommend using LIMIT and OFFSET with an ORDER BY clause. Otherwise, the results are returned in a random order, producing unpredictable results.