Limiting Results — LIMIT / OFFSET

LIMIT restricts the number of rows returned by a query to at most the specified number. If the query would normally return 10,000 rows, LIMIT 10 returns only 10. LIMIT is evaluated last in the SQL execution order — after FROM, WHERE, GROUP BY, HAVING, SELECT, and ORDER BY — so it cuts the final result set after all other processing is complete.

ORDER BY is critical when using LIMIT because without it, LIMIT returns an arbitrary set of rows — whichever rows the database encounters first in its internal storage scan. This order is non-deterministic and can vary between executions as storage layouts change. LIMIT 10 without ORDER BY answers "give me any 10 rows," which is useful only for sampling. LIMIT 10 with ORDER BY unit_price DESC answers "give me the 10 most expensive rows" — a specific, reproducible, meaningful result.

The combination ORDER BY column DESC LIMIT n is also a performance pattern: if the sort column has an index, the database reads the index in order and stops after n rows — never reading the rest of the table. Without an index, the database uses a top-N heap sort that is far cheaper than a full sort followed by truncation. In both cases, LIMIT + ORDER BY is much more efficient than returning all rows and discarding most of them in application code.

OFFSET tells the database to skip a specified number of rows before starting to return results. LIMIT 10 OFFSET 20 skips the first 20 rows and returns the next 10 — rows 21 through 30. OFFSET is almost always used with LIMIT. Without LIMIT, OFFSET alone means "skip these rows and return everything remaining" — useful occasionally but not the standard use case.

The pagination formula for page-based navigation: OFFSET = (page_number - 1) × page_size. For a 10-items-per-page list: page 1 → OFFSET 0, page 2 → OFFSET 10, page 3 → OFFSET 20, page N → OFFSET (N-1)×10. The query structure is always: SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT page_size OFFSET (page_number - 1) * page_size.

To know how many total pages exist: run a separate SELECT COUNT(*) with the same WHERE clause, then calculate total_pages = CEIL(total_count / page_size). Applications typically run both queries per page request — the count query for the pagination controls and the data query for the actual rows. This two-query pattern is standard in REST APIs, GraphQL resolvers, and database-backed dashboards.

The deep pagination problem is the performance degradation that occurs with large OFFSET values. To execute LIMIT 10 OFFSET 990000, the database must process 990,010 rows — skip 990,000 and return 10. Even though only 10 rows are returned, the work of scanning through 990,000 rows cannot be avoided. Performance degrades linearly with OFFSET size: page 1 takes 1ms, page 100,000 takes 100,000ms. On tables with billions of rows, deep pagination with large OFFSETs becomes functionally unusable.

The solution is cursor-based pagination (also called keyset pagination). Instead of "skip N rows," use "give me rows after this specific cursor value." For example, instead of LIMIT 10 OFFSET 990000, store the last order_id from the previous page (say, order_id = 991000) and run: WHERE order_id > 991000 ORDER BY order_id ASC LIMIT 10. Because order_id is the primary key and always indexed, this WHERE clause does a direct index lookup regardless of how deep into the table it is — O(1) performance on page 1 and page 1,000,000 alike.

The trade-off: cursor-based pagination cannot jump to an arbitrary page number (you cannot say "go to page 5000" without knowing the cursor for page 4999). It only supports "next page" and sometimes "previous page." This is why Instagram, Twitter, and product catalogues with infinite scroll use cursor-based pagination — users always go next, never jump to page 5000. Admin dashboards and reports where users need to jump to specific pages sometimes still use OFFSET despite the performance cost, accepting the trade-off for the UX benefit.

WHERE and LIMIT both reduce the number of rows returned, but they operate at fundamentally different points in the execution pipeline and serve entirely different purposes. WHERE is a filter — it specifies which rows qualify to be in the result at all, based on their values. It runs early in the execution order (after FROM, before GROUP BY, SELECT, ORDER BY) and can use indexes to avoid reading rows that do not match. WHERE changes which rows are in the result set.

LIMIT is a cap — it stops returning rows after the specified count regardless of how many qualifying rows remain. It runs last in the execution order, after all other processing including ORDER BY. LIMIT does not filter based on values — it simply truncates the result at N rows. LIMIT changes how many rows from the result set are returned to the caller, not which rows qualify.

Performance implication: WHERE on an indexed column can reduce the rows the database processes from billions to hundreds — a massive speedup applied early. LIMIT reduces the rows returned to the caller but the database may still process many rows to find them, especially without an index or with a large OFFSET. A query with no WHERE and LIMIT 10 on a billion-row table without an appropriate index still scans the full table (or sorts it). Always use WHERE to filter the relevant data, then LIMIT to cap the output size. They are complementary, not interchangeable.

LIMIT n OFFSET m is the most common syntax and works in PostgreSQL, MySQL, SQLite, DuckDB, and MariaDB. OFFSET is optional — LIMIT n alone returns the first n rows with no skipping. This syntax is clean and widely understood, making it the de facto standard for most modern development.

SQL Server uses a different syntax that is part of the SQL standard: ORDER BY column OFFSET m ROWS FETCH NEXT n ROWS ONLY. Important: SQL Server requires ORDER BY when using OFFSET/FETCH — you cannot use it without specifying a sort order. Oracle 12c and later uses the same FETCH NEXT syntax. Older Oracle versions (pre-12c) required a ROWNUM workaround: SELECT * FROM (SELECT *, ROWNUM rn FROM table WHERE ROWNUM <= n + m) WHERE rn > m — cumbersome and now rarely needed.

MySQL has a shorthand form LIMIT m, n where the first argument is the offset and the second is the count — the reverse of LIMIT n OFFSET m. This form is valid MySQL but widely considered confusing because the argument order is counterintuitive. Avoid it in shared codebases. PostgreSQL supports both LIMIT/OFFSET and FETCH NEXT, making it compatible with both MySQL-style and SQL Server-style code. When writing SQL that must run on multiple database types, use FETCH FIRST n ROWS ONLY (no offset) or OFFSET m ROWS FETCH NEXT n ROWS ONLY — these are part of the SQL standard and work on PostgreSQL, SQL Server, Oracle, and DB2.

Cause: LIMIT is placed before WHERE in the query, but LIMIT must always be the last clause (or second to last, before a final FETCH). The correct order is: SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT. Placing LIMIT anywhere else causes a syntax error because the parser expects WHERE before any result-limiting clauses.

Fix: Always write LIMIT as the very last clause: SELECT ... FROM ... WHERE status = 'Delivered' ORDER BY order_date DESC LIMIT 10. If you are also using OFFSET, LIMIT comes before OFFSET: LIMIT 10 OFFSET 20. Check your clause order whenever you see a syntax error near LIMIT — 90% of the time it is a clause ordering issue.

Cause: You used LIMIT without ORDER BY. Without an explicit sort order, the database returns rows in whatever order its internal storage or execution plan produces, which is non-deterministic and can change between runs as storage changes, parallel execution varies, or buffer cache state differs. Two identical LIMIT 10 queries without ORDER BY may return completely different rows.

Fix: Always combine LIMIT with ORDER BY when you need a specific set of rows: LIMIT 10 ORDER BY order_date DESC returns the 10 most recent orders consistently. If you genuinely want a random sample with no specific order (e.g. for QA testing), that is the only legitimate case for LIMIT without ORDER BY — and even then, document in a comment that the random order is intentional.

Cause: The ORDER BY columns used for pagination have duplicate values (ties), and no unique tiebreaker column is included. When two rows have identical values in all ORDER BY columns, the database can return them in any order — and the order may differ between the page 1 query and the page 2 query. A row that appeared at position 10 (end of page 1) may shift to position 11 (start of page 2) in the next query, causing it to appear on both pages.

Fix: Always add the primary key as the final ORDER BY column: ORDER BY order_date DESC, order_id DESC. Since order_id is unique for every row, there are no ties and every row has a fixed, deterministic position in the result. This guarantees that LIMIT 10 OFFSET 0, LIMIT 10 OFFSET 10, and LIMIT 10 OFFSET 20 return perfectly non-overlapping pages with no duplicates or gaps.

Cause: The OFFSET value is too large — either because the total row count is less than the offset, or (the more common bug) the page number was used directly as the offset instead of (page_number - 1) * page_size. If page_size = 10 and the user requests page 2, the offset should be (2-1)*10 = 10. If the code incorrectly uses page_number directly as the offset: LIMIT 10 OFFSET 2 returns rows 3-12 for page 2 (not page 2 at all), LIMIT 10 OFFSET 10 returns rows 11-20 for page 10 (wrong page), and eventually the offset exceeds the total row count returning nothing.

Fix: Verify the pagination formula: OFFSET = (page_number - 1) * page_size. For page_number = 2, page_size = 10: OFFSET = (2-1)*10 = 10. Run the count query first: SELECT COUNT(*) FROM table WHERE ...; confirm the total. Then manually verify LIMIT 10 OFFSET 0 returns rows 1-10, LIMIT 10 OFFSET 10 returns rows 11-15 (and shows 5 rows, not 10, because there are only 15 total). If any of these return unexpected counts, the OFFSET formula is wrong.

Cause: Large OFFSET values are expensive because the database must count through all skipped rows before returning results. To execute OFFSET 500000, the database processes 500,020 rows — it cannot jump directly to row 500,001 without counting. This is O(offset) complexity, meaning performance degrades linearly as users paginate deeper into the dataset. The query plan shows a Sequential Scan or full index scan over hundreds of thousands of rows.

Fix: Switch to cursor-based pagination: instead of LIMIT 20 OFFSET 500000, store the last id from the previous page and use WHERE id > last_id ORDER BY id ASC LIMIT 20. This WHERE condition on the primary key does an O(1) index lookup regardless of depth — page 25,000 is as fast as page 1. If OFFSET-based pagination must be retained (e.g. users need to jump to specific page numbers), add a covering index on the sort column, limit the maximum allowed page depth, or pre-compute and cache deep pages during off-peak hours.

The FreshMart mobile app shows customers a 'Recent Orders' screen with 4 orders per page. Write the SQL for page 1 and page 2 of all delivered orders, sorted by most recent first. Also write the query to find how many total pages the customer needs to scroll through.