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.