SQL and MySQL course · Module 2: Querying data (SELECT)
OFFSET and pagination
In this lesson6
The merchant from Rhodes came back with a new request: he wants to browse the whole catalog on his wax tablet, five titles at a time, like the pages of a book. LIMIT alone always takes the first rows. But what if you want the second page of the catalog - the next five books after those first five? Here OFFSET steps in.
Pages of the catalog
Let us arrange all the titles alphabetically and split them into pages of 5 entries. Seventeen scrolls make three full pages and one shorter page:
| Page | Positions | Titles | LIMIT | OFFSET |
|---|---|---|---|---|
| 1 | 1-5 | Ajaks, Antygona, Dziady, Elementy, Etyka | 5 | 0 |
| 2 | 6-10 | Fajdros, Hamlet, Iliada, Krzyżacy, Medea | 5 | 5 |
| 3 | 11-15 | Odyseja, Pan Tadeusz, Państwo, Polityka, Potop | 5 | 10 |
| 4 | 16-17 | Tom Sawyer, Uczta | 5 | 15 |
The OFFSET column says how many rows to skip before a given page begins: the first page skips nothing, the second skips the five scrolls of the first, the third skips ten. LIMIT is always 5, because that is the page size. The last page has only two entries, because that is how many scrolls are left.
OFFSET - skipping rows
OFFSET says how many rows to skip before you start counting. This is how you fetch the third page of the catalog:
SELECT tytul
FROM ksiazki
ORDER BY tytul ASC
LIMIT 5 OFFSET 10;Here we skip 10 scrolls and take the next 5 (that is, positions 11-15):
| tytul |
|---|
| Odyseja |
| Pan Tadeusz |
| Państwo |
| Polityka |
| Potop |
This is exactly the third page from the table above. That is what pagination is - splitting a long list into pages.
A formula for the page
You do not have to count on your fingers. The formula is simple, and you can write it in a comment in the query:
-- OFFSET = (page_number - 1) * page_size
SELECT tytul FROM ksiazki ORDER BY tytul ASC LIMIT 5 OFFSET 5;The above shows the second page (we skip the first 5 and take the next 5):
| tytul |
|---|
| Fajdros |
| Hamlet |
| Iliada |
| Krzyżacy |
| Medea |
For the third page the formula gives (3 - 1) * 5 = 10, and for the fourth (4 - 1) * 5 = 15. Only OFFSET changes; LIMIT stays the same.
Pagination needs ORDER BY
Notice that every query in this lesson has ORDER BY. That is no accident. Without a fixed order the Library may give the scrolls in a different order with every query. Then "skipping the first ten" has no stable meaning: the same title may land on two pages, and another may not appear on any. That is why we always paginate a sorted list - preferably sorted by a field with no ties, such as id.
The comma form
In other people's code you will also meet a shorthand that MySQL accepts instead of the word OFFSET:
SELECT tytul FROM ksiazki ORDER BY tytul ASC LIMIT 10, 5;The result is the same as with LIMIT 5 OFFSET 10:
| tytul |
|---|
| Odyseja |
| Pan Tadeusz |
| Państwo |
| Polityka |
| Potop |
Watch the order of the numbers: in the comma form the number of rows to skip comes first, and the number of rows to show comes second - the opposite of the OFFSET form. It is easy to get confused, which is why I always write LIMIT … OFFSET … - and that is what I recommend.
The order of clauses and common mistakes
We append OFFSET after LIMIT, at the very end of the query. The full order reads: SELECT → FROM → ORDER BY → LIMIT → OFFSET. Two mistakes happen most often:
- Swapped numbers.
LIMIT 10 OFFSET 5skips five rows and shows ten - instead of one page you get two glued together. LIMIT is the page size, OFFSET is the number of skipped rows. - OFFSET without LIMIT. In MySQL,
OFFSET 10on its own, without LIMIT, is a syntax error. OFFSET always goes together with LIMIT.
Thanks to pagination, even the longest catalog fits comfortably on the screen - page by page, scroll by scroll.
Spotted a mistake in this lesson?
Check yourself
Answer the questions from this lesson. Pick an answer to see right away whether it is correct.
1. What will the clause LIMIT 5 OFFSET 10 return (with a fixed sort order)?
2. With 5 entries per page, what OFFSET must you give to show the third page of results?
Hands-on tasks in the game
- Code editor
Write a query that retrieves all columns from the czytelnicy table, sorts the rows by id, skips the first 10 and shows the next 5 (use ORDER BY, LIMIT and OFFSET).
- Click in order
Click the elements in the order in which you write the query tail that fetches the second page of 5 rows:
- Code editor
Write a query that retrieves the tytul column from the ksiazki table, sorts ascending by title and shows the second page of 5 entries (skip 5 rows, show the next 5).