Paging: LIMIT, OFFSET … FETCH, TOP, ROWNUM

Every example below returns the same ten rows: products 21 to 30 by id.

SQLite, MySQL, MariaDB, PostgreSQL

These four databases use LIMIT <row count> OFFSET <rows to skip>, so the same query runs on all four.

-- Rows 21 to 30, ordered by id.
SELECT id, name, price
FROM products
ORDER BY id
LIMIT 10 OFFSET 20;

SELECT id, name, price
FROM products
ORDER BY id
LIMIT 10 OFFSET 20;

-- The older comma form means the same thing, with the arguments reversed:
-- offset first, then count. Easy to misread, so prefer the form above.
SELECT id, name, price
FROM products
ORDER BY id
LIMIT 20, 10;

MySQL and MariaDB also accept LIMIT 20, 10. Here, 20 is the offset and 10 is the number of rows to return. It is equivalent to LIMIT 10 OFFSET 20, but the order is easy to misread; use the OFFSET form in new queries.

SELECT id, name, price
FROM products
ORDER BY id
LIMIT 10 OFFSET 20;

-- PostgreSQL also accepts the SQL standard spelling.
SELECT id, name, price
FROM products
ORDER BY id
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

SQL Server

-- OFFSET / FETCH requires an ORDER BY. Without one it is a syntax error.
SELECT id, name, price
FROM products
ORDER BY id
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

-- TOP caps the result but cannot skip, so it only gives you the first page.
SELECT TOP 10 id, name, price
FROM products
ORDER BY id;

SQL Server differs in two ways. OFFSET … FETCH follows ORDER BY rather than standing alone, so an ORDER BY is required. The older TOP syntax cannot skip rows, so it only supports the first page.

Planche therefore uses two forms for SQL Server. Editor row limits use TOP n after SELECT. The paged Open Table grid uses OFFSET n ROWS FETCH NEXT m ROWS ONLY together with ORDER BY.

Oracle

-- Oracle 12c and later.
SELECT id, name, price
FROM products
ORDER BY id
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

-- Before 12c, ROWNUM had to be applied to an already-sorted subquery.
SELECT * FROM (
  SELECT t.*, ROWNUM AS rn FROM (
    SELECT id, name, price FROM products ORDER BY id
  ) t
  WHERE ROWNUM <= 30
)
WHERE rn > 20;

Oracle 12c (2013) added the standard row-limit clause. FETCH FIRST n ROWS ONLY alone returns only the first page. Earlier versions used ROWNUM, which is assigned before sorting. Therefore WHERE ROWNUM <= 10 ORDER BY price sorts an arbitrary ten rows rather than returning the top ten. The example works around this by sorting in the inner query, numbering in the middle, and filtering in the outer query.

What OFFSET actually costs

An engine cannot start directly at the ten-thousandth row. It produces rows in order and discards those before the offset, so OFFSET 10000 reads 10,010 rows to return 10. Later pages therefore become progressively slower.

When the rows have a unique, ordered key, ask for the next page by that key instead:

-- The same page without OFFSET: continue after the last id of the
-- previous page. The database jumps straight there through the index.
SELECT id, name, price
FROM products
WHERE id > 20
ORDER BY id
LIMIT 10;

Instead of “skip 20,” this asks to start after id 20, which an index can find directly. Deep pages cost about the same as early ones. The trade-off is that you can move forward and backward but cannot jump to an arbitrary page number, so this suits infinite scrolling and cursor APIs. The continuation condition must use the same, unique ordering columns: for ORDER BY price, id, use WHERE (price, id) > (:price, :id). PostgreSQL, MySQL 8, SQLite, and Oracle support this directly; SQL Server does not.