SQL ROWNUM

What is SQL ROWNUM?

Oracle-specific pseudocolumn that assigns a sequential integer, starting at 1, to each row returned by a query.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL ROWNUM: Supported: Oracle Database, Oracle Autonomous Database, Amazon Aurora (for Oracle compatibility), some older TimesTen versions. Not supported: PostgreSQL, MySQL, SQL Server, SQLite, Snowflake.

SQL ROWNUM Full Explanation

ROWNUM is a virtual column automatically added to the result set of every SELECT statement executed in Oracle Database. Evaluation happens before ORDER BY and after the rows are fetched, meaning the numbering reflects the row generation order, not any later sort. Because ROWNUM is calculated on the fly, it is neither stored nor indexed and resets each time the query runs. Key behaviors:- Starts at 1 for the first returned row and increments by 1 for each subsequent row.- Evaluated before ORDER BY, so combining a simple WHERE ROWNUM <= N with ORDER BY may yield unexpected rows. Use a subquery that orders first, then filter by ROWNUM in an outer query.- Cannot test for ROWNUM = n directly except in hierarchical queries; use ROWNUM <= n or wrap in a subquery and compare a derived alias.- Works only in Oracle (and compatible engines such as Amazon Aurora for Oracle); other databases use LIMIT, TOP, or the ROW_NUMBER() analytic function instead.

SQL ROWNUM Syntax

--Select ROWNUM as an output column
SELECT column_list,
       ROWNUM
FROM   table_name;

--Limit rows
SELECT *
FROM   table_name
WHERE  ROWNUM <= 10;

--Proper ordering then limiting
SELECT *
FROM  (
  SELECT *
  FROM   table_name
  ORDER  BY created_at DESC
)
WHERE  ROWNUM <= 5;

SQL ROWNUM Parameters

Example Queries Using SQL ROWNUM

--1. Display each employee with its row number
SELECT employee_id,
       first_name,
       ROWNUM AS seq
FROM   hr.employees;

--2. Return only the first 10 products
SELECT *
FROM   products
WHERE  ROWNUM <= 10;

--3. Get the five most recently created users (correct ordering)
SELECT *
FROM (
  SELECT *
  FROM   users
  ORDER  BY created_at DESC
)
WHERE ROWNUM <= 5;

Expected Output Using SQL ROWNUM

Adds an extra column named SEQ showing 1,2,3… in query order.
Returns at most 10 product rows.
Returns the newest five users after applying the desired ORDER BY.

Use Cases with SQL ROWNUM

Paginating or limiting results without FETCH FIRST or LIMIT
Quickly numbering rows in ad-hoc scripts
Performance testing by sampling the first N rows
Building hierarchical or nested queries that need sequential reference numbers

Common Mistakes with SQL ROWNUM

Applying ORDER BY after filtering with ROWNUM, producing unexpected rows
Expecting ROWNUM to persist or be indexed like a normal column
Using ROWNUM = N instead of ROWNUM <= N or a subquery alias
Assuming ROWNUM exists in non-Oracle databases

First Introduced In

Oracle Database V2 (1979)

Frequently Asked Questions

What is the difference between ROWNUM and ROW_NUMBER()?

ROWNUM is a pseudocolumn calculated before ORDER BY; ROW_NUMBER() is an analytic function calculated after ORDER BY, allowing partitioning and deterministic ordering.

How can I paginate results using ROWNUM?

Nest two subqueries: order the results in the innermost query, filter with ROWNUM <= high_value in the middle query, then pick rows greater than low_value in the outer query.

Does ROWNUM slow down queries?

ROWNUM itself is virtually cost-free, but wrapping queries in subqueries to use it can prevent index usage and may increase memory. Use FETCH FIRST or OFFSET in newer Oracle versions when possible.

Can I update or insert ROWNUM?

No. ROWNUM is generated at query runtime and cannot be inserted, updated, or indexed. Use a real column or sequence if persistent numbering is required.