Using ROWNUM for pagination with ORDER BY

Use FETCH FIRST (Oracle 12c+) or ROW_NUMBER() for pagination

ROWNUM is assigned before ORDER BY is applied. This causes unexpected behavior when trying to paginate sorted results.

Example - ROWNUM pagination (WRONG):

-- Attempt to get rows 11-20 sorted by order_date

SELECT *

FROM orders

WHERE ROWNUM BETWEEN 11 AND 20 -- This returns 0 rows!

ORDER BY order_date DESC;

-- ROWNUM is assigned before ORDER BY, so ROWNUM > 10 is evaluated first

-- No rows have ROWNUM > 10 initially, so query returns nothing

-- Workaround with subquery (complex):

SELECT *

FROM (

SELECT orders.*, ROWNUM AS rn

FROM (

SELECT *

FROM orders

ORDER BY order_date DESC

) orders

WHERE ROWNUM <= 20

)

WHERE rn >= 11;

-- Works but requires nested subqueries

-- CORRECT: Use FETCH FIRST (Oracle 12c+)

SELECT *

FROM orders

ORDER BY order_date DESC

OFFSET 10 ROWS

FETCH FIRST 10 ROWS ONLY;

-- Clear, ANSI standard, works as expected

-- CORRECT: Use ROW_NUMBER()

SELECT *

FROM (

SELECT orders.*, ROW_NUMBER() OVER (ORDER BY order_date DESC) AS rn

FROM orders

)

WHERE rn BETWEEN 11 AND 20;

-- Works in all Oracle versions, flexible for complex windowing

Use FETCH FIRST for new code (Oracle 12c+). Use ROW_NUMBER() for older versions or complex windowing. Avoid ROWNUM for pagination.