OFFSET and pagination

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.

OFFSET - skipping rows

OFFSET

says how many rows to skip before you start counting:

1SELECT tytul
2FROM ksiazki
3ORDER BY tytul ASC
4LIMIT 5 OFFSET 10;

Here we skip 10 scrolls and take the next 5 (that is, positions 11-15). This is the basis of pagination - splitting a long list into pages.

Pages of the catalog

Imagine a catalog with 5 entries per page:

| Page | LIMIT | OFFSET | |------|-------|--------| | 1 | 5 | 0 | | 2 | 5 | 5 | | 3 | 5 | 10 |

The formula is simple:

1-- OFFSET = (page_number - 1) * page_size
2SELECT 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).

Order of clauses

We append OFFSET after LIMIT, at the very end of the query. The full order reads: SELECT → FROM → ORDER BY → LIMIT → OFFSET.

Thanks to pagination, even the longest catalog fits comfortably on the screen - page by page, scroll by scroll.