Oracle Pagination: ROWNUM vs FETCH FIRST (and the ROWNUM > 1 Trap)

Posted by Kyle Hankinson July 24, 2026

---

If you learned SQL on MySQL or PostgreSQL, your first attempt to limit rows in Oracle probably looked like this:

SELECT id, total FROM orders LIMIT 10;

Oracle rejects it. On a current release (I tested against Oracle Database Free 23ai in Docker) the parser says:

ORA-03047: number '10' is not syntactically valid following
'...id, total FROM orders LIMIT '

Older versions raise the vaguer ORA-00933: SQL command not properly ended. Either way, there is no LIMIT keyword in Oracle. There are two replacements: the standard row-limiting clause added in Oracle 12.1, and the much older ROWNUM pseudocolumn, which carries two traps that produce wrong results without any error message. Here is the modern syntax first, then what ROWNUM actually does, because you will still meet it in old code and old answers.

The modern way: FETCH FIRST (Oracle 12.1 and later)

Oracle 12.1 (2013) added the SQL-standard row limiting clause:

SELECT id, customer, total
FROM   orders
ORDER  BY total DESC
FETCH  FIRST 10 ROWS ONLY;

Pagination adds an OFFSET. Page 3 with 10 rows per page:

SELECT id, customer, total
FROM   orders
ORDER  BY total DESC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

I ran both against a 100-row test table and they behave exactly like LIMIT/OFFSET elsewhere. Two details worth knowing:

• The ORDER BY is technically optional, but without it Oracle hands back an arbitrary 10 rows, so pages can overlap or skip rows between requests. Always order by something unique (add the primary key as a tie-breaker).
• FETCH FIRST 3 ROWS WITH TIES keeps rows that tie with the last one, and FETCH FIRST 10 PERCENT ROWS ONLY works too.

If you are on 12.1 or later, use this and stop reading here if you like. The rest of the article is about the pre-12c pattern and the two ways it silently goes wrong.

What ROWNUM actually is

ROWNUM is a pseudocolumn that numbers rows in the order Oracle fetches them from the filtered result set. The first row that passes the WHERE clause gets ROWNUM 1, the second gets 2, and so on. Two consequences follow directly from that definition, and each one is a classic bug.

Trap 1: WHERE ROWNUM > 1 returns zero rows, always

SELECT id, customer, total FROM orders WHERE ROWNUM > 1;

Against my 100-row table:

no rows selected

No error, no warning, just an empty result. The mechanics: the first candidate row is offered ROWNUM 1. The predicate 1 > 1 is false, so the row is rejected. Because it was rejected, the counter never advances, and the next candidate row is offered ROWNUM 1 again. Every row fails the same test forever. Oracle's own documentation states that conditions testing for ROWNUM values greater than a positive integer are always false.

The same logic kills WHERE ROWNUM = 5, which also returned no rows selected in my test. Only ROWNUM = 1 and ranges anchored at 1 (ROWNUM <= n, ROWNUM < n) can ever be true. If you need "rows 21 to 30", you cannot do it with a bare ROWNUM predicate; you need one of the patterns below.

Trap 2: ROWNUM is assigned before ORDER BY

This one is nastier because it returns plausible-looking data. The intent here is "the ten biggest orders":

-- WRONG: filters first, sorts the leftovers
SELECT id, customer, total
FROM   orders
WHERE  ROWNUM <= 10
ORDER  BY total DESC;

Oracle applies ROWNUM <= 10 while fetching, which grabs the first ten rows in whatever order the table returns them, and only then sorts those ten. Against my test table (100 orders with random totals), the wrong query returned ids 1 through 10, nicely sorted, including rows with totals of 60.61 and 55.73. The correct version pushes the ORDER BY into a subquery so sorting happens before ROWNUM is assigned:

-- RIGHT: sort first, then take the top of the sorted set
SELECT id, customer, total
FROM  (SELECT id, customer, total FROM orders ORDER BY total DESC)
WHERE ROWNUM <= 10;

That returned a genuinely different set: the true top ten, with the lowest total at 458.01. Seven of the ten rows differed between the two queries. Nothing about the wrong query's output hints that it is wrong, which is why this bug survives code review.

Offset pagination before 12c

On 11g and earlier, "skip 20, take 10" needs a double-nested query, because ROWNUM must be materialized in an inner block before you can filter on its higher values:

SELECT id, customer, total
FROM (
  SELECT t.*, ROWNUM AS rn
  FROM  (SELECT id, customer, total FROM orders ORDER BY total DESC) t
  WHERE ROWNUM <= 30
)
WHERE rn > 20;

The analytic alternative reads more clearly and gives the same rows (I verified both return identical output for the same page):

SELECT id, customer, total
FROM (
  SELECT o.*, ROW_NUMBER() OVER (ORDER BY total DESC) AS rn
  FROM   orders o
)
WHERE rn BETWEEN 21 AND 30
ORDER BY rn;

If ROW_NUMBER is new to you, our ROW_NUMBER vs RANK vs DENSE_RANK post covers how the numbering functions differ.

Quick reference

Oracle version	Top-N query	Offset pagination
12.1 and later	ORDER BY ... FETCH FIRST n ROWS ONLY	ORDER BY ... OFFSET m ROWS FETCH NEXT n ROWS ONLY
11g and earlier	ordered subquery + WHERE ROWNUM <= n	double-nested ROWNUM or ROW_NUMBER() OVER (...)
Any version, deep pages	keyset (seek) pagination	keyset pagination

That last row deserves a sentence. OFFSET-style pagination reads and throws away every skipped row, so page 5,000 is expensive. Keyset pagination filters on the last value seen instead:

SELECT id, customer, total
FROM   orders
WHERE  total < :last_total
   OR (total = :last_total AND id < :last_id)
ORDER  BY total DESC, id DESC
FETCH  FIRST 10 ROWS ONLY;

I verified this picks up exactly where the previous page ended. It cannot jump to an arbitrary page number, but for infinite-scroll style access it scales far better. For a cross-engine view of LIMIT, TOP, and FETCH FIRST, see how to limit query results and paginate in SQL.

A convenient way to convince yourself of the ORDER BY trap is to run the wrong and right variants in two query tabs of SQLPro Studio against an Oracle connection and compare the result grids side by side; the diverging rows are hard to miss. If you want a sandbox to try it on, one command gives you a disposable Oracle on an ARM or Intel Mac: docker run -d -p 1521:1521 -e ORACLE_PASSWORD=test gvenzl/oracle-free:23-slim, then connect to service FREEPDB1.

The habit that keeps you safe: never filter on ROWNUM in the same query block as an ORDER BY, and never compare ROWNUM against anything other than a range starting at 1. On any Oracle from the last decade, skip the ceremony and write FETCH FIRST.

---