Pages of Results with OFFSET

A shop with thousands of games doesn't show them all at once. It shows, say, twenty on a page, with buttons for the next pages. In SQL, you make pages with OFFSET, which skips the given number of rows before LIMIT starts counting:

SELECT name, city
FROM customers
ORDER BY name
LIMIT 5 OFFSET 5;

That's the second page of the customers, five per page: OFFSET 5 skips the first five (Ava Green to Isla Young), and LIMIT 5 shows the next five. The first page has OFFSET 0, which is the same as no OFFSET at all, and the third page has OFFSET 10. In general, a page skips the rows of all the pages before it: (the number of the page − 1) × the number of rows per page.

The last page can be shorter. The shop has 14 customers, so the third page has only four. And if OFFSET skips all the rows, the result is empty: just the names of the columns.

Pages need a sort that gives one exact order. If two rows were a tie, the database could show one of them at the end of the first page and again at the start of the second, and another one on neither. That's why pages are sorted by something that doesn't repeat, or with a column such as id as the last sort column.

SQLite also understands a shorter way to write it, LIMIT 5, 5, but careful: there, the number of rows to skip comes first, so LIMIT 10, 5 skips ten rows and shows five. That's easy to mix up, so this course writes OFFSET.