How to Use ROW_NUMBER() Function

Syntax

ROW_NUMBER() OVER ( [PARTITION BY expr [, ...]] ORDER BY expr [ASC | DESC] [, ...] )

• ORDER BY inside OVER is required. It decides which row gets 1, which gets 2, and so on.
• PARTITION BY is optional. With it, numbering restarts at 1 for each distinct value. Without it, the whole result set is one sequence.
• The parentheses after ROW_NUMBER stay empty. The return type is bigint.

The ORDER BY inside OVER controls numbering only. It does not sort the output. If you want the rows displayed in order, add an ordinary ORDER BY at the end of the query as well.

Number every row

SELECT ROW_NUMBER() OVER (ORDER BY OrderDate, OrderId) AS rn,
       OrderId, CustomerId, OrderDate, Amount
FROM dbo.Orders
ORDER BY rn;

rn  OrderId  CustomerId  OrderDate   Amount
--  -------  ----------  ----------  ------
1   1        101         2026-08-01  250.00
2   3        102         2026-08-03   80.00
3   2        101         2026-08-15  120.00
4   4        101         2026-09-02  300.00
5   5        102         2026-09-10   80.00
6   6        103         2026-09-11  500.00

Number within groups

SELECT ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY OrderDate DESC) AS rn,
       CustomerId, OrderId, OrderDate, Amount
FROM dbo.Orders
ORDER BY CustomerId, rn;

rn  CustomerId  OrderId  OrderDate   Amount
--  ----------  -------  ----------  ------
1   101         4        2026-09-02  300.00
2   101         2        2026-08-15  120.00
3   101         1        2026-08-01  250.00
1   102         5        2026-09-10   80.00
2   102         3        2026-08-03   80.00
1   103         6        2026-09-11  500.00

Latest row per group

You cannot filter on ROW_NUMBER() in the same query that computes it. Window functions are evaluated after WHERE, so this fails:

SELECT OrderId, ROW_NUMBER() OVER (ORDER BY OrderDate) AS rn
FROM dbo.Orders
WHERE ROW_NUMBER() OVER (ORDER BY OrderDate) = 1;

Msg 4108: Windowed functions can only appear in the SELECT or ORDER BY clauses.

Referring to the alias (WHERE rn = 1) fails too, with "Invalid column name 'rn'". Compute the number in a CTE or derived table and filter outside it:

WITH ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY OrderDate DESC, OrderId DESC) AS rn
    FROM dbo.Orders
)
SELECT CustomerId, OrderId, OrderDate, Amount
FROM ranked
WHERE rn = 1;

CustomerId  OrderId  OrderDate   Amount
----------  -------  ----------  ------
101         4        2026-09-02  300.00
102         5        2026-09-10   80.00
103         6        2026-09-11  500.00

Change the filter to rn <= 3 for the three most recent orders per customer.

Delete duplicates

Number the rows within each set of duplicates and delete every row numbered above 1. Suppose two orders by the same customer for the same amount count as duplicates, and you want to keep the earliest:

BEGIN TRANSACTION;

WITH d AS (
    SELECT ROW_NUMBER() OVER (PARTITION BY CustomerId, Amount ORDER BY OrderDate, OrderId) AS rn
    FROM dbo.Orders
)
DELETE FROM d
WHERE rn > 1;

SELECT OrderId FROM dbo.Orders;   -- check the result

ROLLBACK TRANSACTION;             -- or COMMIT once you are satisfied

In the sample data this removes order 5, the second 80.00 order from customer 102. Deleting through the CTE deletes from the underlying table. The example rolls back so the remaining examples still see all six rows. On real data, keep the transaction, check what was deleted, and commit only when the result is right.

Paging

The classic pattern numbers the rows and selects a range:

DECLARE @PageNumber int = 2, @PageSize int = 2;

WITH numbered AS (
    SELECT ROW_NUMBER() OVER (ORDER BY OrderDate, OrderId) AS rn,
           OrderId, CustomerId, OrderDate, Amount
    FROM dbo.Orders
)
SELECT OrderId, CustomerId, OrderDate, Amount
FROM numbered
WHERE rn BETWEEN (@PageNumber - 1) * @PageSize + 1 AND @PageNumber * @PageSize
ORDER BY rn;

Page 2 with a page size of 2 returns rows 3 and 4: orders 2 and 4.

For plain paging, OFFSET and FETCH do the same job with less code and are the usual choice now:

SELECT OrderId, CustomerId, OrderDate, Amount
FROM dbo.Orders
ORDER BY OrderDate, OrderId
OFFSET (@PageNumber - 1) * @PageSize ROWS FETCH NEXT @PageSize ROWS ONLY;

Keep the ROW_NUMBER() form when you also need the row number in the output, or when you page within groups.

Both approaches read and discard all the rows before the requested page, so page 5,000 is much slower than page 1. For deep paging, filter on the last key the client saw, for example WHERE OrderId > @LastSeenOrderId ORDER BY OrderId, and fetch the next page from there.

Ties make the numbering non-deterministic

If two rows have the same values for every ORDER BY column, SQL Server may number them either way, and the choice can change between executions. ORDER BY Amount is ambiguous for orders 3 and 5 in the sample data, which both have 80.00.

This is the cause of most ROW_NUMBER() bugs: a "latest row per group" query that returns different rows on different days, a page that shows a row twice, a de-duplication that keeps a different copy each run. The fix is always the same. End the ORDER BY with a column that is unique, as the examples above do with OrderId.

ROW_NUMBER, RANK and DENSE_RANK

The three functions differ only in how they treat ties:

SELECT OrderId, Amount,
       ROW_NUMBER() OVER (ORDER BY Amount DESC, OrderId) AS row_num,
       RANK()       OVER (ORDER BY Amount DESC)          AS rnk,
       DENSE_RANK() OVER (ORDER BY Amount DESC)          AS dense_rnk
FROM dbo.Orders
ORDER BY Amount DESC, OrderId;

OrderId  Amount  row_num  rnk  dense_rnk
-------  ------  -------  ---  ---------
6        500.00  1        1    1
4        300.00  2        2    2
1        250.00  3        3    3
2        120.00  4        4    4
3         80.00  5        5    5
5         80.00  6        5    5

Use ROW_NUMBER() when you need exactly one row per group. Use RANK() or DENSE_RANK() when tied rows should all be returned.

When you do not care about the order

ORDER BY is mandatory, and it does not accept a constant: OVER (ORDER BY 1) is an error. To number rows in whatever order the engine produces them, use a subquery that returns a constant:

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn, OrderId
FROM dbo.Orders;

This avoids a sort, but the numbering is arbitrary and can differ between runs. It is fine for generating a sequence, and wrong for anything that must be repeatable.

Performance

SQL Server has to see the rows of each partition in ORDER BY order. Without a suitable index, that means a Sort operator over the whole input, which needs memory and can spill to tempdb on large tables.

An index whose keys are the PARTITION BY columns followed by the ORDER BY columns, and which includes the other columns the query returns, removes the sort:

CREATE INDEX IX_Orders_Customer_Date
    ON dbo.Orders (CustomerId, OrderDate DESC, OrderId DESC)
    INCLUDE (Amount);

That index supports the "latest row per group" query above directly. Apply any WHERE filter that does not depend on the row number inside the CTE, so fewer rows are numbered.

Further reading

• ROW_NUMBER (Transact-SQL)
• SELECT - OVER clause (Transact-SQL)
• Ranking functions (Transact-SQL)

What causes slow queries in SQL Server, and how do I fix them?

A diagnostic catalogue of slow query causes in SQL Server: how to tell running from waiting, the plan or wait signature of each cause, and the fix.

#execution-plans#performance#troubleshooting