ORDER BY and LIMIT

ORDER BY and LIMIT

Sorting and Paging with ORDER BY and LIMIT

Tables have no inherent order. ORDER BY sorts on columns or expressions, ASC by default or DESC; LIMIT n OFFSET m (or MySQL 524 's LIMIT m, n) skips m rows and returns n.

Ties, then a tie-breaker for page 2SQL

SELECT id, sku, category_id FROM products ORDER BY category_id LIMIT 3;
SELECT id, sku, category_id FROM products ORDER BY category_id, id LIMIT 3 OFFSET 3;

Output

+----+-----------+-------------+
| id | sku       | category_id |
+----+-----------+-------------+
|  1 | BK-PHP-01 |           2 |
| 11 | BK-PHP-03 |           2 |
|  6 | BK-LNX-01 |           2 |
+----+-----------+-------------+
3 rows in set (0.000 sec)
+----+-----------+-------------+
| id | sku       | category_id |
+----+-----------+-------------+
|  9 | BK-PHP-02 |           2 |
| 11 | BK-PHP-03 |           2 |
|  2 | BK-SQL-01 |           3 |
+----+-----------+-------------+
3 rows in set (0.000 sec)

Five products share category 2, and the first query returned three in arbitrary order. Tied rows may come back in any order, even differently with and without LIMIT, so pages can repeat or skip rows. End every paging sort with a unique column, as the second query does. NULL sorts first in ascending order.

OFFSET still reads every skipped row, so deep pages slow down. Keyset pagination remembers the last row's sort key instead: WHERE (category_id, id) > (2, 11) ORDER BY category_id, id LIMIT 3 returns the next page directly, and with an index every page costs the same (Keyset Pagination).

MENU