LIMIT vs TOP vs FETCH FIRST vs ROWNUM: Pagination Syntax Compared

Quick comparison table

MySQL & PostgreSQL: LIMIT / OFFSET

SELECT product_name, price
FROM products
ORDER BY price DESC
LIMIT 10 OFFSET 20;

This skips the first 20 rows of the sorted result and returns the next 10 — page 3 of a 10-per-page listing. MySQL also accepts the older shorthand LIMIT 20, 10 (offset first, count second), which is easy to misread and worth avoiding in new code.

SQL Server: TOP and OFFSET/FETCH

-- Simple "top N", no offset
SELECT TOP (10) product_name, price
FROM products
ORDER BY price DESC;

-- Paging (SQL Server 2012+)
SELECT product_name, price
FROM products
ORDER BY price DESC
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY;

TOP alone has no built-in offset — it only takes the first n rows of the sorted result. For actual pagination, SQL Server needs the standard OFFSET ... FETCH NEXT ... ROWS ONLY form, which — like TOP — requires an ORDER BY to be meaningful.

Oracle 12c+: FETCH FIRST

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

Oracle Database 12c introduced this standard syntax, matching PostgreSQL and modern SQL Server exactly. If you're on Oracle 12c or later, there is no reason to reach for ROWNUM at all — use this instead.

Oracle legacy: the ROWNUM trap

On Oracle versions before 12c — or in older code you're maintaining — ROWNUM is the only option, and it has a well-known gotcha: ROWNUM is assigned before ORDER BY runs. Writing the filter directly alongside a sort looks reasonable but silently returns the wrong rows:

-- Looks correct, isn't
SELECT product_name, price
FROM products
WHERE ROWNUM <= 10
ORDER BY price DESC;

Oracle assigns ROWNUM values to rows as it reads them off disk or index, then applies the WHERE filter — all before the ORDER BY sort happens. So WHERE ROWNUM <= 10 grabs 10 essentially arbitrary rows first, and only then sorts that small, already-wrong set by price. The fix is to force the sort to happen first, in an inner query, and apply ROWNUM to its already-ordered output:

SELECT product_name, price
FROM (
    SELECT product_name, price
    FROM products
    ORDER BY price DESC
)
WHERE ROWNUM <= 10;

The inner query sorts the full table by price first; the outer query's ROWNUM filter is then applied to that already-correctly-ordered result, so the 10 rows it keeps are genuinely the top 10 by price.

Why ORDER BY is non-negotiable

None of these four mechanisms — LIMIT, TOP, FETCH FIRST, or ROWNUM — guarantee which rows come back unless the query also has an ORDER BY. Without one, the database returns whatever rows it finds most convenient to read first, based on storage layout, index usage, or query plan — an order that can silently change between runs, after an index rebuild, or after a version upgrade, even though the query text never changed.