LIMIT and OFFSET for pagination

Concepts covered: Row Limiting

LIMIT cuts the result to its first n rows. OFFSET skips the first m rows before the cut begins. Together they carve a sorted result into pages: page 1 is OFFSET 0 LIMIT 20, page 2 is OFFSET 20 LIMIT 20, and page k is OFFSET (k - 1) * 20 LIMIT 20. In the query text OFFSET follows ORDER BY and comes before LIMIT. The products table below has 8 rows, paged 3 at a time.

Page 1

Skip nothing and keep the first 3 rows of the sorted result.

SELECT
  product_id,
  name,
  price
FROM products
ORDER BY
  price DESC,
  product_id
OFFSET 0
LIMIT 3

Page 2

Skip 3 rows and keep the next 3.

SELECT
  product_id,
  name,
  price
FROM products
ORDER BY
  price DESC,
  product_id
OFFSET 3
LIMIT 3

Page 3

Skip 6 rows and keep whatever is left, up to 3.

SELECT
  product_id,
  name,
  price
FROM products
ORDER BY
  price DESC,
  product_id
OFFSET 6
LIMIT 3

Page 3 returns 2 rows because only 8 exist: OFFSET 6 skips 6, LIMIT 3 asks for 3, and the table has 2 left. A page shorter than the page size tells the caller it has reached the end, and page 4 confirms it with no rows at all.

The products table is sorted by price DESC, then product_id. Which rows does OFFSET 2 LIMIT 2 return?

• Monitor and Chair, the 2 most expensive products
• Chair and Headset, starting from the second row
• Headset and Keyboard, the third and fourth rows
• Keyboard and Webcam, the 2 rows priced at 90

ORDER BY is the contract

Without ORDER BY the engine may return rows in any order, and the order can change between 2 runs of the same query. Page 1 and page 2 might then both contain Monitor while Chair appears on neither. The sort also has to be total: Monitor and Chair share the price 310, and Keyboard and Webcam share 90, so ORDER BY price DESC alone leaves the engine to break ties however it likes, and a tie can straddle a page boundary. Adding product_id as the final sort key gives every row a fixed position, so each product appears on exactly one page.

What OFFSET costs

OFFSET walks past rows rather than jumping over them. To produce page 5,001 of 20 rows, the engine sorts the whole table, passes the first 100,000 rows one by one, then keeps 20. Every page pays for the full sort plus the skipped rows, so later pages run slower than earlier ones, and a crawler requesting page 50,000 keeps the engine sorting rows that no one will see.

• 100,020
• 5,000

Keyset pagination

Instead of counting rows to skip, remember the last row of the current page and ask for rows that sort after it. Page 2 ended on Desk Lamp, price 45, product_id 1. The next page is every row with a lower price, or the same price and a higher product_id. That filter mixes OR and AND, so it needs the parentheses from the last section.

SELECT
  product_id,
  name,
  price
FROM products
WHERE price < 45
  OR (price = 45 AND product_id > 1)
ORDER BY
  price DESC,
  product_id
LIMIT 3

The result matches page 3 exactly: Mouse, then Cable. The difference is the work done. The WHERE removed 6 rows before the sort, and on sorted storage the engine lands on price 45 directly instead of reading and discarding everything above it. The trade is that keyset pages come in sequence: there is no way to ask for page 40 without first learning where page 39 ended.

OFFSET paging

• Jump to any page number directly
• Cost grows with the page number: sorts and skips every earlier row
• Rows inserted between 2 requests shift the later pages
• Fits a numbered page bar in a UI

Keyset paging

• Reach pages in sequence from the last seen row
• Cost stays flat: the filter lands on the next row
• Rows inserted earlier in the sort do not shift the next page
• Fits a Next button or an infinite scroll

About this lesson

This section is part of the Filtering: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.