CSE Roadmap

Core tier · about 3 hours · 5 parts

Part 4 of 5 · 2 min read

Paging with LIMIT and OFFSET

Reading back only a slice of a large, sorted result.

Paging with LIMIT and OFFSET

Think of reading only the first ten entries of a long, sorted phone book, then later reading entries eleven through twenty.

In plain words: LIMIT caps how many rows a query returns. OFFSET skips a number of rows before results start. Paired with ORDER BY, they let an application read a large result one page at a time.

A worked example. Reading the second page of ten most recent orders, meaning rows eleven through twenty once sorted.

The key lines.

SELECT order_id, placed_on
FROM orders
ORDER BY placed_on DESC
LIMIT 10 OFFSET 10;

LIMIT, OFFSET

LIMIT caps the number of rows a query returns. OFFSET skips a given number of rows before returning results. Both only behave predictably when the query also has an ORDER BY.

Example: LIMIT 10 OFFSET 10 returns rows eleven through twenty of the sorted result.

Remember this

Without ORDER BY, a database is free to return rows in any order it finds convenient internally. LIMIT and OFFSET without ORDER BY can return a different, unpredictable page each time the same query runs.

For the curious Why OFFSET gets slow on large tables

OFFSET works by reading and discarding every skipped row before returning the next page. Reading page two hundred of a result still means reading and discarding the first nineteen hundred rows. Level 6's module on pagination covers a faster alternative: keyset pagination.

Quick notes

  • WHERE filters rows using comparison operators, combined with AND and OR.
  • ORDER BY sorts a result; ASC is the default, DESC reverses it; multiple columns break ties in order.
  • LIMIT caps how many rows come back; OFFSET skips rows before returning results.
  • LIMIT and OFFSET only give a stable, predictable page when paired with ORDER BY.
  • OFFSET gets slower as the skipped amount grows, since the database still reads every skipped row.