What is ROWNUM?

ROWNUM is a pseudocolumn in Oracle SQL that assigns a unique, sequential number to each row returned by a query result.

• The numbering starts from 1.
• It is assigned before ORDER BY, so the result set is not sorted when ROWNUM is applied.
• It’s often used for pagination, filtering top-N results, or limiting rows.

Syntax

SELECT ROWNUM, column1, column2
FROM your_table;

You can also filter rows using ROWNUM:

SELECT *
FROM your_table
WHERE ROWNUM <= 5;

This fetches only the first 5 rows returned by the query.

Important Behavior of ROWNUM

Rule	Explanation
1	ROWNUM is assigned after WHERE clause, but before ORDER BY
2	You cannot use ROWNUM > 1 directly, as it evaluates before assignment
3	To filter rows after ordering, use subqueries

Why Doesn’t ROWNUM > 1 Work?

Example:

SELECT *
FROM employees
WHERE ROWNUM > 1;  -- ❌ Will return 0 rows

Why?

• ROWNUM = 1 is assigned first.
• The row is filtered out because 1 > 1 is false.
• The next row never gets a ROWNUM.

Correct Way: Use a Subquery

If you want to skip the first row, do this:

SELECT *
FROM (
  SELECT ROWNUM AS rn, e.*
  FROM employees e
)
WHERE rn > 1;

This works because:

• The ROWNUM is assigned inside the subquery.
• The outer query then filters based on that assigned value.

Difference Between ROWNUM and ROW_NUMBER()

Feature	ROWNUM	ROW_NUMBER()
Oracle Version	Built-in since early versions	Requires Oracle 12c or analytic function support
Sorting	Applied before ORDER BY	Applied after ORDER BY
Use in Pagination	Less flexible	More flexible
Supports Partitioning	No	Yes (via PARTITION BY)
Reset per group	No	Yes

Examples

Example 1: Get Top 3 Highest Paid Employees (Incorrect)

SELECT *
FROM employees
WHERE ROWNUM <= 3
ORDER BY salary DESC;

Issue: The ROWNUM is applied before sorting, so this may return any 3 rows, not the top 3 by salary.

Example 2: Get Top 3 Highest Paid Employees (Correct)

SELECT *
FROM (
  SELECT *
  FROM employees
  ORDER BY salary DESC
)
WHERE ROWNUM <= 3;

Why this works:

• The inner query sorts by salary first.
• Then ROWNUM is assigned in the outer query after sorting.

Example 3: Get 6th to 10th Rows (Pagination)

SELECT *
FROM (
  SELECT e.*, ROWNUM AS rn
  FROM (
    SELECT *
    FROM employees
    ORDER BY salary DESC
  ) e
  WHERE ROWNUM <= 10
)
WHERE rn >= 6;

Steps:

1. Inner-most query sorts data.
2. Middle query assigns ROWNUM.
3. Outer query filters to get rows 6 to 10.

Common Use Cases

Use Case	Example
Limit rows	WHERE ROWNUM <= 10
Pagination	Use nested queries with ROWNUM range
Top-N Queries	Select top N by column using ORDER BY + ROWNUM
Benchmarking	Quickly limit number of rows for test

Alternative: FETCH FIRST (Oracle 12c+)

In Oracle 12c and above, a better way to limit rows is:

SELECT *
FROM employees
ORDER BY salary DESC
FETCH FIRST 5 ROWS ONLY;

For pagination:

SELECT *
FROM employees
ORDER BY salary DESC
OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;

Benefits over ROWNUM:

• Easier syntax
• Works with ORDER BY naturally
• More readable

Summary

Feature	ROWNUM
What is it?	A pseudocolumn that numbers result rows
Starts from?	1
Assigned when?	After WHERE, before ORDER BY
Can I use ROWNUM > 1?	❌ Not directly — use a subquery
Best for	Limiting results, basic pagination
Replaced by (in 12c+)	FETCH FIRST / OFFSET syntax