Querying
LIMIT and OFFSET
LIMIT and OFFSET restrict how many rows a query returns and where the result set begins.
Overview
LIMIT and OFFSET restrict how many rows a query returns and where the result set begins. OFFSET skips a number of rows before returning results, while LIMIT caps the number of rows returned after that skip. Together they implement pagination, letting an application fetch one page of rows at a time instead of the entire result. The SQL standard introduced equivalent functionality in SQL-2008 as OFFSET ... FETCH, and many systems also accept the shorter LIMIT ... OFFSET spelling. Because row order is not guaranteed unless you specify it, LIMIT and OFFSET are almost always paired with ORDER BY. Without a deterministic sort, the same query can return different rows across executions, which breaks pagination. These clauses apply after filtering, grouping, and sorting, so they operate on the final result set. They are widely used for web pagination, batch processing, and sampling, but they are not a substitute for a WHERE predicate when you need a specific subset of data. Vendor support varies: PostgreSQL, MySQL, MariaDB, and SQLite accept LIMIT/OFFSET, while SQL Server uses OFFSET ... FETCH and Oracle supports both forms in recent versions.
Syntax and usage
The common form is SELECT ... FROM t ORDER BY col LIMIT n OFFSET m. Here n is the maximum number of rows to return and m is the number of rows to skip. Either clause may be omitted: LIMIT n alone returns the first n rows, and OFFSET m alone skips m rows and returns the rest. Standard SQL uses OFFSET m ROWS FETCH NEXT n ROWS ONLY, which is supported by PostgreSQL, Oracle, SQL Server, and DB2. MySQL and SQLite support LIMIT n OFFSET m and also LIMIT m, n, where the first number is the offset and the second is the count; this comma form is not portable. Some systems require an ORDER BY when OFFSET is used, and SQL Server requires ORDER BY for OFFSET ... FETCH. LIMIT and OFFSET accept non-negative integers; expressions and parameters are allowed in most engines. A value of zero for LIMIT returns no rows, and an OFFSET beyond the result size returns an empty set. These clauses appear at the end of the SELECT statement, after ORDER BY, and cannot be used in subqueries in every engine.
How it works
Logically, the query first produces a result set through FROM, WHERE, GROUP BY, HAVING, and SELECT, then ORDER BY sorts it. LIMIT and OFFSET are applied last. OFFSET discards the first m rows of the sorted result, and LIMIT then returns at most n of the remaining rows. Because sorting must happen before rows can be skipped or counted, the engine may need to materialize or sort the full result even when only a few rows are returned. This is why deep offsets are expensive: OFFSET 1000000 forces the database to generate and discard a million rows. Indexes on the ORDER BY columns can allow ordered scans that avoid a full sort, but the skipped rows are still traversed. Without ORDER BY, the order is implementation-defined and may change between runs, so pagination can repeat or miss rows. LIMIT and OFFSET do not change the semantics of aggregation; they only trim the final output. They also do not affect which rows are computed, so a query with a large OFFSET still performs the same work as one without it, minus the final transfer.
Examples
Return the ten highest-paid employees: SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 10. Skip the first twenty rows and return the next ten, a common page-two query: SELECT id, title FROM articles ORDER BY published_at DESC LIMIT 10 OFFSET 20. The standard spelling: SELECT id FROM orders ORDER BY id OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY. The MySQL comma form: SELECT id FROM orders ORDER BY id LIMIT 40, 20. A keyset-style alternative that avoids deep offsets: SELECT id, created_at FROM events WHERE created_at < :last_seen ORDER BY created_at DESC LIMIT 20. Here the application remembers the last row of the previous page and filters with WHERE instead of skipping rows. To sample a few rows without a defined order, SELECT * FROM logs LIMIT 5 returns an arbitrary five rows. To count total rows for pagination, run a separate SELECT COUNT(*) FROM articles query, since LIMIT does not affect aggregates in the same statement.
Common pitfalls
The most frequent mistake is using LIMIT and OFFSET without ORDER BY. The database may return rows in any order, so pages can overlap or skip records. Another issue is unstable sort keys: if ORDER BY uses a column with duplicate values, rows with equal keys may appear in different orders across queries, causing duplicates or omissions at page boundaries. Deep offsets are slow because the engine still reads and discards all skipped rows; performance degrades as the offset grows. Mixing the MySQL comma form LIMIT offset, count with the standard LIMIT count OFFSET offset can confuse readers and is not portable. Some engines reject OFFSET without ORDER BY, and SQL Server requires ORDER BY for OFFSET ... FETCH. Negative or non-integer values raise errors. Finally, LIMIT does not limit the work done by joins or aggregations; it only limits returned rows, so a query that joins large tables can still be expensive even with a small LIMIT.
Best practices
Always pair LIMIT and OFFSET with a deterministic ORDER BY, ideally on a unique or nearly unique key such as a primary key, or add a tiebreaker column. Prefer keyset pagination for large or frequently paged data: filter with WHERE on the last seen key instead of using a large OFFSET. Keep page sizes reasonable and avoid unbounded OFFSET values in user-facing endpoints. Use parameters for LIMIT and OFFSET rather than string concatenation to prevent injection and enable plan reuse. When you need the total count, run a separate COUNT query or use window functions where supported. Be explicit about the syntax your target database supports, and prefer the standard OFFSET ... FETCH form when portability matters. Test pagination with duplicate sort values to confirm stability. Remember that LIMIT applies after ORDER BY, so place it at the end of the statement. Document the expected ordering in APIs, since clients depend on it for consistent paging.
Frequently asked questions
What is the difference between LIMIT and OFFSET? LIMIT sets the maximum number of rows returned, while OFFSET skips a number of rows before returning results. They are often used together for pagination. Does LIMIT require ORDER BY? Standard SQL does not require it, but without ORDER BY the row order is undefined, so results may vary between executions. Most practitioners treat ORDER BY as mandatory when paginating. Why is OFFSET slow for large values? The database must still produce and sort the full result, then discard the skipped rows, so the cost grows with the offset. Keyset pagination, which filters with WHERE on the last seen key, usually performs better. Is LIMIT portable? The LIMIT ... OFFSET spelling is widely supported but not universal; SQL Server uses OFFSET ... FETCH, and standard SQL defines OFFSET ... FETCH. Check your database's documentation before relying on a specific form.