SQL and MySQL course · Module 2: Querying data (SELECT)

OFFSET and pagination

4 min read ·
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:

PagePositionsTitlesLIMITOFFSET
11-5Ajaks, Antygona, Dziady, Elementy, Etyka50
26-10Fajdros, Hamlet, Iliada, Krzyżacy, Medea55
311-15Odyseja, Pan Tadeusz, Państwo, Polityka, Potop510
416-17Tom Sawyer, Uczta515

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 5 skips 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 10 on 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. 1. What will the clause LIMIT 5 OFFSET 10 return (with a fixed sort order)?

  2. 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).

  1. QuestionHow LIMIT with OFFSET works
  2. QuestionOFFSET formula for pagination
  3. Coding taskPaginating readers
  4. Click in orderSecond page of 5 rows
  5. Coding taskSecond page of books

Useful articles