SQL ROW_NUMBER

What is SQL ROW_NUMBER?

Assigns a unique sequential number to each row within a partition of a result set, ordered by specified columns.

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 ROW_NUMBER:

SQL ROW_NUMBER Full Explanation

ROW_NUMBER is a window (analytic) function defined in the SQL:2003 standard. It scans the rows returned by a query, groups them into optional partitions, orders each partition by the given columns, and then labels the rows 1, 2, 3, … in that order. The numbering restarts for every partition. Because the numbering is deterministic when ORDER BY is unambiguous, ROW_NUMBER is ideal for pagination, top-N reports, de-duplicating identical rows, or building deterministic surrogate keys in result sets. Unlike RANK and DENSE_RANK, ROW_NUMBER never produces gaps because each row receives exactly one distinct integer. If ORDER BY is omitted (only valid in some dialects) the numbering order is implementation-dependent, so always specify ORDER BY for predictable output. ROW_NUMBER is evaluated after WHERE, GROUP BY, and HAVING but before the final ORDER BY of the SELECT statement. It cannot be used in WHERE directly; wrap the query in a subquery or common table expression (CTE) and filter on the derived column.

SQL ROW_NUMBER Syntax

SELECT column_list,
       ROW_NUMBER() OVER (
           [PARTITION BY partition_expression]
           ORDER BY sort_expression [ASC|DESC]
       ) AS row_num
FROM   table_name;

SQL ROW_NUMBER Parameters

PARTITION BY (expression) - Divides the result set into independent groups before numbering.
ORDER BY (expression) - Defines the sort order inside each partition and therefore the numbering sequence.
ASC (DESC) - keyword|||Sort direction (default ASC).

Example Queries Using SQL ROW_NUMBER

-- 1. Basic usage: global numbering
SELECT id, created_at,
       ROW_NUMBER() OVER (ORDER BY created_at DESC) AS row_num
FROM   events;

-- 2. Restart numbering per customer
SELECT order_id, customer_id, order_date,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS rn
FROM   orders;

-- 3. Keep the latest record per user with a CTE
WITH ranked AS (
  SELECT user_id, status, updated_at,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY updated_at DESC) AS rn
  FROM user_status_history
)
SELECT user_id, status, updated_at
FROM   ranked
WHERE  rn = 1;

Expected Output Using SQL ROW_NUMBER

Each query returns the original columns plus an extra column (row_num or rn) containing integers starting at 1
In example 3 only the most recent status row per user is kept because rn = 1 filters out the others

Use Cases with SQL ROW_NUMBER

Paginating large result sets by selecting rows WHERE row_num BETWEEN x AND y
Selecting the top N rows per category (e.g., top 3 salespersons per region)
Removing duplicate rows while keeping the earliest or latest record
Creating deterministic row ordering for UI tables
Building slowly changing dimension loaders that pick the latest record per business key

Common Mistakes with SQL ROW_NUMBER

Forgetting ORDER BY, leading to nondeterministic numbering.
Using ROW_NUMBER in WHERE instead of a subquery or CTE (produces "window function not allowed in WHERE" error).
Expecting ROW_NUMBER to skip gaps like RANK; it never does.
Assuming ROW_NUMBER ordering matches the final SELECT ORDER BY; they are independent clauses.

First Introduced In

SQL:2003

Frequently Asked Questions

What does ROW_NUMBER return?

It returns an integer starting at 1 for the first row in each ordered partition and increases by 1 for every subsequent row.

How do I select the first row per group using ROW_NUMBER?

Wrap the query in a CTE, compute ROW_NUMBER partitioned by the group column and ordered by your preference, then filter WHERE row_num = 1.

Is ROW_NUMBER faster than RANK or DENSE_RANK?

Performance is usually identical because all three are evaluated in the same window-function phase. Choose based on the numbering semantics you need, not speed.

Does ROW_NUMBER work without PARTITION BY?

Yes. If PARTITION BY is omitted the function treats the entire result set as a single partition and numbers all rows globally.