OFFSET

The OFFSET clause skips a specified number of rows in a query's result set before returning the remaining rows, commonly used for pagination alongside LIMIT.

Overview

OFFSET tells a query how many rows to skip from the beginning of the (typically ordered) result set before it starts returning rows. On its own it's rarely used without LIMIT -- the pair together implement classic page-based pagination: "give me rows 21 through 30."

Copy code
SELECT id, name
FROM customers
ORDER BY id
LIMIT 10 OFFSET 20;   -- skip the first 20 rows, return the next 10

Why ORDER BY matters

Like LIMIT, OFFSET only produces a meaningful, repeatable result when the query has a deterministic ORDER BY. Without one, "the first 20 rows" is not well-defined, and different executions (or query plans) could skip a different set of rows.

DuckDB notes

DuckDB implements the standard LIMIT n OFFSET m syntax and, like LIMIT, allows OFFSET to be an arbitrary expression rather than only a literal integer.

Performance

OFFSET is conceptually simple but can be a performance trap at scale: to skip the first 1,000,000 rows, the database generally still has to produce and discard them, so OFFSET cost grows with its value. For deep pagination over large tables, a keyset (a.k.a. "seek") approach -- filtering with WHERE on the last row's sort key instead of using OFFSET -- avoids that cost:

Copy code
-- instead of LIMIT 20 OFFSET 100000
SELECT id, name FROM customers
WHERE id > 100000   -- last id seen on the previous page
ORDER BY id
LIMIT 20;

Related terms

LIMIT →

The LIMIT clause restricts a query's result set to a maximum number of rows, often used with ORDER BY to fetch a top-N subset.

LAG and LEAD →

LAG() and LEAD() are window functions that return a value from a previous or following row within the same result set, commonly used for period-over-period comparisons.

ROW_NUMBER →

ROW_NUMBER() is a SQL window function that assigns a unique, sequential integer to each row within a partition based on a specified order, with no ties.

WHERE clause →

The WHERE clause filters rows in a SQL query based on a boolean condition, keeping only rows for which the condition evaluates to true.

ORDER BY clause →

The ORDER BY clause is a fundamental SQL command that lets you control the sequence in which your query results are returned.

OVER clause →

The OVER clause turns an aggregate or ranking function into a window function by defining the partition, order, and frame it operates on, without collapsing the result set.

FAQS

Syntactically yes in most databases including DuckDB, but it's uncommon -- without a LIMIT, OFFSET just skips rows and returns everything after them, which is rarely what you want on a large table.

The database typically still has to scan and discard the skipped rows, so cost scales with the offset value; keyset pagination (filtering on the last seen key) avoids this by jumping directly to the right position.