LIMIT, TOP, and FETCH in SQL

Introduction

LIMIT, TOP, and FETCH restrict the number of rows returned by a SQL query. They solve the same broad problem using syntax associated with different database systems. LIMIT is common in MySQL, PostgreSQL, and SQLite; TOP is strongly associated with SQL Server; and FETCH FIRST or OFFSET FETCH follows a SQL-standard style supported by several products.

-- MySQL, PostgreSQL, and SQLite
SELECT product_id, product_name, price
FROM products
ORDER BY price DESC, product_id
LIMIT 5;

Row limiting is useful only when its meaning is clear. ORDER BY defines which rows come first, while LIMIT, TOP, or FETCH defines how much of that ordered result is returned. Without deterministic ordering, a query may return the requested number of rows but not a predictable set of rows.

Why Row Limiting Is Needed

An application rarely needs every row from a large operational table. A dashboard may show ten recent orders, a search screen may display twenty products, and a worker may claim one eligible job. Returning a million rows wastes database work, network bandwidth, application memory, serialization time, and browser resources.

A row limit establishes a bounded result contract. It can also enable efficient Top-N plans, but it is not a substitute for WHERE, appropriate projection, or indexing. The database may still examine many rows before it knows which limited rows qualify.

The Core Row-Limiting Concept

ORDER BY and row limiting answer different questions. ORDER BY answers which rows should appear first. LIMIT, TOP, or FETCH answers how many rows from that sequence should be returned. A reliable query specifies both whenever the chosen rows matter.

RELIABLE TOP-N = FILTERED DATA
                 + BUSINESS ORDER
                 + UNIQUE TIE-BREAKER
                 + ROW LIMIT

The tie-breaker is important when several rows share the primary sort value. Ordering recent orders by created_at DESC alone leaves equal timestamps unresolved. Adding order_id DESC creates a complete sequence.

LIMIT Syntax

SELECT column1, column2
FROM table_name
ORDER BY sort_column
LIMIT number_of_rows;

LIMIT appears near the end of the statement and returns at most the requested number of rows. If only three rows qualify for LIMIT 10, the query returns three. LIMIT does not create missing rows or guarantee an exact count.

LIMIT Without ORDER BY

SELECT product_id, product_name
FROM products
LIMIT 5;

This query returns up to five products, but it does not mean the first inserted, lowest ID, oldest, newest, cheapest, or most expensive products. The selected rows can depend on the execution plan, index, storage layout, statistics, parallelism, and concurrent activity.

Unordered limiting can be acceptable for diagnostics or sampling when any rows truly satisfy the requirement. It should not be used where users or downstream systems expect a stable business meaning.

LIMIT with ORDER BY

SELECT product_id, product_name, price
FROM products
ORDER BY price DESC, product_id
LIMIT 5;

This clearly requests the five most expensive products, with product_id resolving equal prices. Reversing price to ASC requests the five cheapest. The unique tie-breaker makes repeated executions stable for an unchanged data snapshot.

Latest and Oldest Rows

SELECT order_id, customer_id, created_at
FROM orders
ORDER BY created_at DESC, order_id DESC
LIMIT 10;

DESC produces the latest ten according to created_at. ASC produces the oldest ten. Choose the timestamp that matches the business event: created_at, submitted_at, paid_at, processed_at, and effective_at answer different questions.

An identity value is not necessarily a reliable time value. Distributed IDs, cached sequences, imports, and delayed commits can break the assumption that the largest ID is always newest.

LIMIT with WHERE

SELECT order_id, created_at, total_amount
FROM orders
WHERE tenant_id = :tenant_id
  AND status = 'PAID'
ORDER BY created_at DESC, order_id DESC
LIMIT 20;

WHERE defines the eligible population before ORDER BY and LIMIT select the leading rows. Filtering by tenant and status is both a business requirement and a performance opportunity. A limit must never be expected to compensate for a missing security or business predicate.

Logical Query Flow

A useful simplified order is FROM and JOIN, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, OFFSET, and LIMIT or FETCH. This explains why LIMIT after GROUP BY restricts output groups rather than source detail rows and why limiting before sorting in application code produces the wrong Top-N result.

The optimizer may use a different physical sequence while preserving these semantics. Execution plans describe the strategy, not a change to the query's logical contract.

LIMIT with DISTINCT

SELECT DISTINCT department
FROM employees
ORDER BY department
LIMIT 10;

The database forms the unique department result, sorts it, and returns at most ten values. Fetching ten employee rows and deduplicating them in application code may return fewer departments and miss values found later in the table.

LIMIT with GROUP BY and HAVING

SELECT customer_id, SUM(total_amount) AS lifetime_value
FROM orders
GROUP BY customer_id
HAVING SUM(total_amount) > 1000
ORDER BY lifetime_value DESC, customer_id
LIMIT 10;

This returns the ten highest-value customers among groups whose lifetime value exceeds 1000. The limit does not reduce the input orders before SUM. If an input subset is required, define it explicitly with WHERE or a carefully ordered subquery and document the changed business meaning.

LIMIT with JOIN

A limit applies to joined result rows, not independently to each table. One customer with many orders can occupy several places in a limited result. Define the intended grain before limiting: one customer, one order, one order item, or one customer-product pair.

For customers who have at least one order, EXISTS may express the relationship better than joining all orders and limiting the expanded result. For one latest order per customer, use a window function or a product-specific lateral lookup rather than a single global LIMIT.

TOP Syntax in SQL Server

SELECT TOP (5)
       product_id, product_name, price
FROM products
ORDER BY price DESC, product_id;

TOP appears immediately after SELECT rather than at the end of the statement. Parentheses are recommended for the numeric expression. Like LIMIT, TOP without ORDER BY returns an arbitrary qualifying subset rather than a meaningful first set.

TOP PERCENT

SELECT TOP (10) PERCENT
       product_id, product_name, price
FROM products
ORDER BY price DESC, product_id;

TOP PERCENT returns a percentage of qualifying rows rather than a fixed count. A changing table therefore produces a changing result size. Use it only when the percentage itself is the requirement, not as an approximate substitute for Top-N.

TOP WITH TIES

SELECT TOP (3) WITH TIES
       employee_id, employee_name, salary
FROM employees
ORDER BY salary DESC;

WITH TIES includes additional rows whose ORDER BY value equals the boundary row. If several employees share the third-highest salary, the query can return more than three employees. This is correct when the requirement is the top three salary ranks rather than exactly three people.

Adding employee_id to ORDER BY makes each combination unique and can prevent useful ties from matching. Decide whether ties are based on salary alone or the full order before adding a technical tie-breaker.

FETCH FIRST Syntax

SELECT product_id, product_name, price
FROM products
ORDER BY price DESC, product_id
FETCH FIRST 5 ROWS ONLY;

FETCH FIRST appears after ORDER BY in SQL-standard-style syntax. Some products accept ROW for one and ROWS for any number. Exact support and requirements vary, so verify the database and version used by the application.

OFFSET and FETCH NEXT

SELECT product_id, product_name, price
FROM products
ORDER BY price DESC, product_id
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY;

OFFSET skips the first twenty ordered rows, and FETCH NEXT returns the next ten. SQL Server requires ORDER BY with OFFSET FETCH. Other databases may use similar syntax or combine OFFSET with LIMIT.

LIMIT, TOP, and FETCH Compared

SyntaxCommon usageTypical position
LIMIT 10MySQL, PostgreSQL, SQLiteNear the end
TOP (10)SQL ServerAfter SELECT
FETCH FIRST 10 ROWS ONLYStandard-style support in several DBMSsAfter ORDER BY

The keywords are not universally interchangeable. Even when two systems support FETCH, details such as ORDER BY requirements, bind parameters, WITH TIES, NULL ordering, and OFFSET behavior can differ. Keep dialect-specific SQL isolated and test it against every supported engine.

Deterministic Top-N Queries

A Top-N query is deterministic when the ORDER BY list defines one stable sequence for all qualifying rows. If the primary business key can tie, add a unique final key that preserves the intended direction.

SELECT event_id, event_time, event_type
FROM events
WHERE tenant_id = :tenant_id
ORDER BY event_time DESC, event_id DESC
LIMIT 50;

Without event_id, rows sharing event_time can cross the limit boundary unpredictably. This can create flickering dashboards, duplicate pagination records, and inconsistent tests.

NULL Placement at the Boundary

Database defaults differ on whether NULL sorts before or after non-NULL values. A limited query can therefore return very different rows across engines when its sort key is nullable. Use NULLS FIRST or NULLS LAST where supported, or a CASE expression for a portable rule.

ORDER BY CASE WHEN completed_at IS NULL THEN 1 ELSE 0 END,
         completed_at DESC,
         task_id

Define what NULL means in the business process. An unfinished task, unknown date, and not-applicable date may require different ordering or separate status columns.

Custom Business Ordering

SELECT ticket_id, priority, created_at
FROM tickets
WHERE status = 'OPEN'
ORDER BY CASE priority
           WHEN 'CRITICAL' THEN 1
           WHEN 'HIGH' THEN 2
           WHEN 'MEDIUM' THEN 3
           ELSE 4
         END,
         created_at,
         ticket_id
LIMIT 20;

CASE expresses a business sequence that alphabetical order cannot. For stable, reused categories, a reference table with an explicit sort rank may be easier to maintain and index.

OFFSET Pagination

For page size P and one-based page number N, the offset is (N - 1) multiplied by P. Page three with twenty rows per page skips forty rows and returns the next twenty.

SELECT product_id, product_name, price
FROM products
WHERE category_id = :category_id
ORDER BY price, product_id
LIMIT 20 OFFSET 40;

Offset pagination is simple and supports direct page-number jumps. It works well for modest result sets and administrative screens where deep navigation is uncommon.

Why Deep OFFSET Can Be Slow

OFFSET does not let the database jump forward for free. The engine often must locate, order, and discard all preceding rows before returning the requested page. Page 10,000 can therefore process a large amount of data to return only twenty rows.

An aligned index helps, but discarding a deep prefix still consumes reads and time. Measure actual deep-page behavior instead of extrapolating from page one.

Keyset Pagination

SELECT order_id, created_at, total_amount
FROM orders
WHERE tenant_id = :tenant_id
  AND (
       created_at < :last_created_at
       OR (created_at = :last_created_at
           AND order_id < :last_order_id)
  )
ORDER BY created_at DESC, order_id DESC
LIMIT 50;

Keyset pagination resumes after the final key from the previous page. It can seek into an aligned index instead of counting and discarding a large offset. The continuation predicate must match every ORDER BY key and direction.

Keyset pagination is ideal for feeds and next-page navigation but is less suitable for arbitrary page-number jumps. The cursor should encode the sort and filter context so it cannot be reused with an incompatible query.

Pagination Under Concurrent Changes

A stable ORDER BY resolves ties but does not freeze the dataset. Inserts, updates, and deletes between page requests can shift offset positions, leading to duplicates or omissions. Keyset pagination generally handles inserts before the cursor more predictably, though updates that move rows across the cursor can still change visibility.

If a report requires a fixed snapshot, consider an appropriate isolation level, a cutoff timestamp, a saved result set, or a report-run identifier. Row limiting defines quantity; transaction and snapshot design define consistency over time.

Top-N per Group

A global LIMIT returns N rows overall, not N rows for each department, customer, or category. Top-N per group requires ranking within each partition.

WITH ranked AS (
  SELECT employee_id,
         department_id,
         salary,
         ROW_NUMBER() OVER (
           PARTITION BY department_id
           ORDER BY salary DESC, employee_id
         ) AS position_in_department
  FROM employees
)
SELECT employee_id, department_id, salary
FROM ranked
WHERE position_in_department <= 3
ORDER BY department_id, position_in_department;

ROW_NUMBER returns exactly three rows per sufficiently large group. RANK or DENSE_RANK can include additional tied rows depending on the required ranking semantics.

Global Limit Versus Per-Branch Limit

A LIMIT after UNION or UNION ALL applies to the combined result. Limiting each branch first is a different operation and requires subqueries with meaningful branch ordering. It can improve performance only when it preserves the intended global result.

For a global latest-events feed assembled from several tables, each branch may provide candidates and the outer query must still order and limit the merged set. Verify that each branch supplies enough candidates to avoid excluding a valid global winner.

LIMIT for Existence Checks

Some code uses SELECT 1 with LIMIT 1 to determine whether a row exists. This can be efficient, but EXISTS usually communicates the requirement more directly inside SQL conditions and lets the optimizer stop after a match.

Use LIMIT 1 when the application actually needs one matching row or its values. Do not select an entire entity merely to test presence.

Latest Single Record

SELECT status_id, status, changed_at
FROM order_status_history
WHERE order_id = :order_id
ORDER BY changed_at DESC, status_id DESC
LIMIT 1;

The ordered limit returns the latest status event according to the chosen event time. Without ORDER BY, LIMIT 1 returns an arbitrary matching history row and can produce severe business errors.

Random Top-N Queries

ORDER BY RANDOM or RAND followed by a limit is convenient for a small table, but it can evaluate a random value for every candidate and sort the entire set. The limit reduces returned rows without necessarily reducing work.

For large datasets, consider database sampling, precomputed random keys, key-range sampling, or application-specific selection. Test both statistical quality and execution cost.

How Indexes Support Top-N

An index aligned with equality filters and ORDER BY columns can let the database seek to the relevant range, read rows in the desired order, and stop after N matches. This is one of the most valuable optimizations for recent-items and queue queries.

-- Query pattern
WHERE tenant_id = :tenant_id
  AND status = 'OPEN'
ORDER BY created_at DESC, ticket_id DESC
LIMIT 20

A composite index beginning with tenant_id and status followed by created_at and ticket_id may support the pattern. Exact key direction, included columns, selectivity, and scan behavior depend on the database.

Top-N Optimization

When a query requests a small number of ordered rows, the optimizer may use an index, bounded heap, or Top-N sort rather than fully sorting every candidate. This can reduce memory and CPU substantially.

The optimization does not guarantee low cost. Expensive joins, unselective filters, computed sort expressions, or many rejected rows can still require substantial work before the first N results are known.

A Limit Is Not a Performance Bandage

Adding LIMIT 10 to a poorly designed query may reduce network transfer while leaving scans, joins, aggregation, and sorting largely unchanged. The database must still establish which ten rows are correct.

Inspect actual plans, rows read, memory, spills, CPU, and duration. Improve predicates, projection, join logic, data types, and indexes according to measured bottlenecks.

API Page-Size Design

APIs should define a default page size and a maximum accepted limit. A client-provided value must be parsed as an integer, bounded to an approved range, and applied through a parameter or trusted query-builder mechanism.

Do not permit unlimited exports through the ordinary list endpoint. Large exports often need asynchronous generation, authorization, rate limits, and separate resource controls. Document default ordering and cursor behavior as part of the API contract.

Safe Dynamic Sorting and Limiting

Limit values can commonly be parameterized, although support differs by driver and TOP syntax. Column names and ASC or DESC keywords cannot generally be represented by ordinary value parameters.

Map public sort names such as newest, oldest, and price_high to fixed reviewed ORDER BY fragments. Reject unknown options and always append the defined tie-breaker. Never concatenate a raw client sort expression into SQL.

Authorization Before Limiting

A limit does not provide authorization. Applying security filtering after fetching a global top ten can expose data or return an underfilled result even when enough authorized rows exist later.

SELECT document_id, title, updated_at
FROM documents
WHERE tenant_id = :tenant_id
  AND access_scope = :allowed_scope
ORDER BY updated_at DESC, document_id DESC
LIMIT 10;

Tenant and access predicates belong in the database query before ordering and limiting. Test cross-tenant scenarios explicitly.

Row Limiting in Batch Processing

Workers often process data in bounded batches to control transaction size and resource use. A safe batch uses a stable order or key range and records progress so retries do not skip or repeat work unexpectedly.

LIMIT alone does not claim rows. Concurrent workers can select the same batch unless the design uses row locks, skip-locked behavior, leases, status transitions, or atomic updates appropriate to the database.

Safe Destructive Batches

Database support for LIMIT or TOP in UPDATE and DELETE varies. Arbitrarily deleting a limited number without deterministic key selection can remove different rows on different executions.

A safer pattern identifies a deterministic key set, modifies exactly those keys in a transaction, commits, and continues from a recorded boundary. The process must account for concurrent inserts, retries, foreign keys, and audit requirements.

Transactions, Locks, and MVCC

LIMIT controls result size, not locking scope. A query may inspect or lock more rows than it returns, depending on predicates, isolation, and plan. Under multi-version concurrency control, repeated limited queries can see different committed snapshots.

Queue consumers and batch jobs need a concurrency protocol in addition to ordering. Review engine-specific locking behavior and test multiple workers rather than assuming LIMIT prevents contention.

Read Replicas and Freshness

A deterministic latest-ten query returns the latest rows visible to the database serving it. A lagging read replica may not contain a newly committed order, so its latest result can differ from the primary even though ordering is correct.

Define read-after-write and freshness requirements. Route sensitive queries appropriately and keep paginated navigation on a consistent source when practical.

Limiting Input Rows with a Subquery

A limit placed in an outer query normally restricts the final result after joins, grouping, and ordering. Sometimes the requirement intentionally limits input before another operation, such as calculating a metric over the most recent one thousand events. That requires an explicit ordered subquery or common table expression.

WITH recent_events AS (
  SELECT event_id, event_time, duration_ms
  FROM events
  WHERE tenant_id = :tenant_id
  ORDER BY event_time DESC, event_id DESC
  FETCH FIRST 1000 ROWS ONLY
)
SELECT AVG(duration_ms) AS recent_average_duration
FROM recent_events;

This average describes the latest one thousand qualifying events, not all events. The inner ORDER BY is meaningful because it determines which rows the inner FETCH retains. Without it, the population would be arbitrary. A limit inside a view or subquery should always be reviewed for this semantic change rather than treated only as an optimization.

Do not push a limit below a join merely to make a query faster unless doing so preserves the required result. Limiting customers before joining orders, for example, can exclude a customer who should rank highly after order totals are aggregated.

Row Goals and Plan Stability

Optimizers can recognize that a query needs only a small number of rows and choose a plan designed to produce early results quickly. This is sometimes described as a row goal. A nested-loop join and ordered index lookup may be excellent for FETCH FIRST 10 even when a scan and hash join would be better for returning the entire dataset.

The chosen plan can become inefficient when the requested limit changes, filters are unusually unselective, or matching rows are sparse. A query that finds ten active records immediately for one tenant may inspect millions of rows for another. Parameter-sensitive planning and data skew therefore matter even when every request has a small output.

Test realistic limit values and representative tenants. Review actual rows read, not only rows returned. Avoid forcing a plan solely because it works for page one under sample data; upgrades, statistics changes, and evolving distributions can alter the most efficient strategy.

Caching and Observability for Limited Results

Limited queries are often cached because dashboards and feeds request them frequently. A cache key must include every filter, tenant, sort option, page size, cursor or offset, permission scope, and relevant locale. Reusing a top-ten result across incompatible contexts can expose data or produce incorrect ordering.

Cache invalidation should reflect the business meaning of latest or highest. A newly created order can invalidate the first page without affecting an older keyset page, while a price update can move a product across several Top-N boundaries. Decide whether short-lived staleness is acceptable and document it.

Operational metrics should separate database rows examined from rows returned. Track latency by endpoint, page depth, limit size, sort option, and tenant. Monitor temporary-sort spills, timeouts, replica lag, and cache hit rates so a small response body does not hide an expensive database operation.

Common Row-Limiting Mistakes

  • Using LIMIT, TOP, or FETCH without a meaningful ORDER BY.
  • Assuming natural, insertion, primary-key, or clustered order.
  • Omitting a unique tie-breaker at a page or Top-N boundary.
  • Sorting in application code after the database already limited rows.
  • Confusing global Top-N with Top-N per group.
  • Using deep OFFSET for every high-volume feed.
  • Assuming LIMIT, TOP, and FETCH syntax is portable unchanged.
  • Allowing unbounded client page sizes or unsafe dynamic sorting.
  • Using LIMIT as a substitute for WHERE or authorization.
  • Assuming a limit always reduces database work.
  • Using arbitrary limits for destructive batches or queue claiming.
  • Expecting pagination to provide snapshot consistency automatically.

Testing Row-Limited Queries

Test zero rows, fewer than N, exactly N, N plus one, many rows, duplicate sort values, NULL sort values, and every supported direction. Boundary tests should verify the exact ordered keys returned, not merely the row count.

For pagination, test adjacent pages, final partial pages, deep offsets, malformed cursors, changed filters, and concurrent inserts, updates, and deletes. For WITH TIES, create ties exactly at the boundary and verify whether the business expects extra rows.

Performance Testing Top-N Queries

Use realistic volume, distribution, tenant skew, row width, and filter selectivity. Measure rows scanned, rows returned, logical reads, CPU, duration, sort memory, temporary spills, and network size.

Compare page one with deep pages and compare offset with keyset pagination. Verify plans for both common and unusual parameter values because one tenant may have ten open tickets while another has ten million.

Production Row-Limiting Checklist

  1. Define what first, latest, highest, or oldest means.
  2. Choose the syntax supported by the target database.
  3. Apply tenant, authorization, and business filters first.
  4. Specify complete ORDER BY directions.
  5. Add a unique tie-breaker when exactly N stable rows are required.
  6. Define NULL and WITH TIES behavior explicitly.
  7. Choose offset or keyset pagination from product requirements.
  8. Bound API page sizes and allowlist sort options.
  9. Use window functions for Top-N per group.
  10. Design indexes for valuable, measured query patterns.
  11. Add a concurrency protocol for workers and batches.
  12. Test boundaries, concurrent changes, deep pages, and execution plans.

Practical Recent-Orders Query

SELECT order_id, customer_id, created_at, total_amount
FROM orders
WHERE tenant_id = :tenant_id
  AND status IN ('PAID', 'SHIPPED')
ORDER BY created_at DESC, order_id DESC
FETCH FIRST 50 ROWS ONLY;

The filter establishes scope, created_at defines recent, order_id resolves ties, and FETCH bounds the result. An aligned index can make this a fast stopping query.

Practical Oldest-Job Queue Query

SELECT job_id, priority, available_at, created_at
FROM jobs
WHERE status = 'READY'
  AND available_at <= CURRENT_TIMESTAMP
ORDER BY priority DESC,
         available_at,
         created_at,
         job_id
FETCH FIRST 1 ROW ONLY;

This defines which job is preferred but does not claim it. Production workers need transactional locking, a lease, or an atomic status update to prevent duplicate processing.

Interview Questions and Answers

What does LIMIT do? It returns at most the requested number of rows, commonly in MySQL, PostgreSQL, and SQLite.

What does TOP do? It limits rows in SQL Server and appears after SELECT.

What does FETCH FIRST do? It uses standard-style syntax to return a bounded number of ordered rows.

Why use ORDER BY? It determines which rows belong in the limited result.

What does OFFSET do? It skips a number of ordered result rows before returning the page.

Why can deep OFFSET be slow? The database often must process and discard the entire preceding prefix.

What is keyset pagination? It continues after the previous page's last ordered key instead of counting an offset.

What is TOP WITH TIES? It includes additional rows equal to the boundary according to ORDER BY.

How do you get Top-N per group? Rank rows with a window function partitioned by the group and filter the rank.

Does LIMIT always improve performance? No. The engine may still scan, join, aggregate, or sort many rows before finding the correct result.

Key Takeaway

LIMIT, TOP, and FETCH are dialect-specific ways to return a bounded portion of a query result. Reliable use depends less on the keyword than on the filters, business ordering, tie semantics, pagination model, security scope, indexes, and concurrency behavior surrounding it.

SAFE ROW LIMITING = AUTHORIZED FILTERED DATA
                    + COMPLETE BUSINESS ORDER
                    + DEFINED TIE BEHAVIOR
                    + APPROPRIATE DIALECT SYNTAX
                    + BOUNDED PAGE SIZE
                    + ALIGNED PAGINATION
                    + MEASURED EXECUTION COST

Never treat a limited unordered result as the first rows in any business sense. Define the sequence first, choose the correct dialect syntax, and test boundaries and performance with realistic data.