How to Use ROWNUM in Oracle
Last updated August 25, 2026 · By the SaturnSQL team
ROWNUM is assigned before ORDER BY runs, so filtering on ROWNUM and sorting in the same query rarely does what you expect.
SELECT employee_id, last_name
FROM employees
WHERE ROWNUM <= 10;The classic bug
WHERE ROWNUM <= 10 ORDER BY salary DESC does not return the 10 highest salaries. Oracle numbers rows 1 through 10 from whatever order it fetches them in, then sorts just those 10. Wrap the ordered query in an inline view and filter ROWNUM on the outside.
SELECT * FROM (
SELECT employee_id, last_name, salary
FROM employees
ORDER BY salary DESC
) WHERE ROWNUM <= 10;ROWNUM > 1 never matches
ROWNUM is generated incrementally as rows pass the filter: the first row is always assigned 1. A condition like WHERE ROWNUM > 1 rejects that first row before a second one can ever become 2, so the query returns nothing. Skipping leading rows needs a subquery that assigns ROWNUM first, then filters on the saved value in an outer query.
In current Oracle, FETCH FIRST n ROWS ONLY replaces ROWNUM for simple row limits and does not have this ordering trap. Keep ROWNUM in mind mainly for reading and fixing legacy code.
Run this in SaturnSQL
SaturnSQL is a browser-based SQL editor for teams: shared query library, schema-aware autocomplete, and scheduled exports to Google Sheets and Slack.
Try it free