Limiting Results and Pagination

LIMIT returns only the first few rows — combined with ORDER BY, it answers "top N" questions.

SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 3;

+-----------------------------+--------+
|            name             | price  |
+-----------------------------+--------+
| 27-inch Monitor             | 249.99 |
| Noise-Cancelling Headphones |    199 |
| Mechanical Keyboard         |     89 |
+-----------------------------+--------+

Without ORDER BY, LIMIT 3 returns some three rows — not reliably the same ones. Always sort before limiting.

Pagination with OFFSET

OFFSET skips rows — the classic way to show page 2, page 3, and so on:

SELECT id, name
FROM customers
ORDER BY id
LIMIT 2 OFFSET 2;

+----+--------------+
| id |     name     |
+----+--------------+
|  3 | Grace Hopper |
|  4 | Alan Turing  |
+----+--------------+

Page n with size rows per page uses LIMIT size OFFSET (n - 1) * size.

Dialect differences

Common mistakes

• Large offsets are slow — the database still reads and discards all the skipped rows. For deep pagination on big tables, "keyset pagination" (WHERE id > last_seen_id ORDER BY id LIMIT 20) is much faster.
• Sorting by a column with ties (such as price) without a tiebreaker can make rows jump between pages. Add a unique column last: ORDER BY price DESC, id.

Practice

Show the two most recent orders.

SELECT id, ordered_on
FROM orders
ORDER BY ordered_on DESC
LIMIT 2;

+----+------------+
| id | ordered_on |
+----+------------+
|  8 | 2025-04-22 |
|  7 | 2025-04-10 |
+----+------------+