Level 3 · Page two of the price list

OFFSET n skips the first n rows of the sorted result. Together with LIMIT, it returns one page:

SELECT product_id, name, unit_price
FROM products
ORDER BY unit_price, product_id
LIMIT 12 OFFSET 12;

Page math

For page p (starting at 1) with size rows per page:

LIMIT size OFFSET (p - 1) * size

Page 1 is OFFSET 0, page 2 is OFFSET 12, page 3 is OFFSET 24. An off-by-one here (using OFFSET 13 for page 2) silently skips a product, which is exactly the kind of bug Ashley wants to catch.

The sort must be unique

Paging only works if every row has one fixed position. If ten products cost $24.99 and you sort by price alone, a product could appear on both page 1 and page 2, or on neither, because the database may order the tied rows differently on each request. Ending the sort with a unique column such as product_id fixes this. This is a real bug in production apps, not a theoretical one.

Dialects

• Postgres, DuckDB, MySQL, BigQuery: LIMIT 12 OFFSET 12.
• MySQL also accepts LIMIT 12, 12 (offset first, then count), a confusing order best avoided.
• SQL standard and SQL Server: OFFSET 12 ROWS FETCH NEXT 12 ROWS ONLY.

OFFSET gets slow on big tables

To return page 5,000 the database still has to produce and throw away the first 59,988 rows. Engineers usually switch to keyset pagination for large lists: remember the last row of the previous page and continue from it.

SELECT product_id, name, unit_price
FROM products
WHERE (unit_price, product_id) > (24.99, 57)
ORDER BY unit_price, product_id
LIMIT 12;

The row comparison (a, b) > (x, y) works in Postgres, DuckDB and MySQL. As an analyst you will rarely write paging for an app, but you will page through query results in tools, and knowing why deep OFFSETs crawl helps you explain slow dashboards.

Common mistakes

• Forgetting ORDER BY. Paging an unsorted result returns arbitrary, overlapping pages.
• Mixing up which number is which. LIMIT is how many rows to return; OFFSET is how many to skip first.