Using ROW_NUMBER() in SQL Server
The ROW_NUMBER() function in SQL Server assigns a unique sequential number to each row returned by a query.
It is particularly useful when you need to rank records, identify duplicates, select the latest record from a group, or implement paging.
ROW_NUMBER() is one of SQL Server's window functions. Unlike an aggregate function such as
SUM() or COUNT(), it does not combine multiple rows into a single result.
Instead, it performs a calculation across a set of rows while still returning each individual row.
Basic ROW_NUMBER() Example
The simplest use of ROW_NUMBER() is to assign a number to every row in a result set.
SELECT ROW_NUMBER() OVER (
ORDER BY CustomerName) AS RowNumber,
CustomerId,
CustomerName
FROM Customers;
The ORDER BY inside the OVER clause controls the order in which the row numbers are assigned.
For example, the result might look like this:
RowNumber CustomerId CustomerName
-----------------------------------------------
1 14 Acme Ltd
2 7 Alpha Systems
3 22 Smith TradingThe row number is calculated after SQL Server has produced the result set. It is not a permanent value stored in the table.
The OVER Clause
ROW_NUMBER() must be followed by an OVER clause.
ROW_NUMBER() OVER (
ORDER BY SomeColumn)
The OVER clause tells SQL Server which rows should be included in the calculation and how those rows should be ordered.
There are two important parts that can be used inside the OVER clause:
ORDER BYcontrols the order in which row numbers are assigned.PARTITION BYdivides the rows into separate groups before numbering them.
Using PARTITION BY
Without PARTITION BY, SQL Server numbers every row in the complete result set.
When PARTITION BY is used, the row numbering restarts from 1 for each group.
For example, the following query numbers orders separately for each customer:
SELECT CustomerId,
OrderId,
OrderDate,
ROW_NUMBER() OVER (PARTITION BY CustomerId
ORDER BY OrderDate) AS CustomerOrderNumber
FROM Orders;Each customer's first order will have a row number of 1, their second order will have a row number of 2, and so on.
CustomerId OrderId OrderDate CustomerOrderNumber
-----------------------------------------------
1 101 2026-01-03 1
1 108 2026-02-14 2
1 117 2026-04-09 3
2 104 2026-01-20 1
2 112 2026-03-05 2Finding the Latest Record for Each Group
One of the most common uses of ROW_NUMBER() is finding the most recent record for each group.
Suppose an order can have several status records and we only want the latest status for each order.
WITH RankedOrderStatuses AS
(SELECT OrderStatusId,
OrderId,
StatusName,
StatusDate,
ROW_NUMBER() OVER (PARTITION BY OrderId
ORDER BY StatusDate DESC) AS RowNumber
FROM OrderStatuses)
SELECT OrderStatusId,
OrderId,
StatusName,
StatusDate
FROM RankedOrderStatuses
WHERE RowNumber = 1;
The records are divided into groups using PARTITION BY OrderId.
Within each group, the records are ordered by StatusDate DESC, which means the newest record is placed first.
That record receives a row number of 1.
The outer query then returns only the records where RowNumber = 1.
This is a very useful pattern:
ROW_NUMBER() OVER (PARTITION BY GroupingColumn
ORDER BY DateColumn DESC)Why a CTE or Subquery Is Required
You cannot normally reference the result of ROW_NUMBER() directly in the
WHERE clause of the same query.
The following will not work:
SELECT OrderId,
CustomerId,
OrderDate,
ROW_NUMBER() OVER (PARTITION BY CustomerId
ORDER BY OrderDate DESC) AS RowNumber
FROM Orders
WHERE RowNumber = 1;
This is because the WHERE clause is logically processed before the
SELECT clause has calculated the row number.
The solution is to calculate the row number inside a CTE or subquery and then filter it in an outer query.
Using a CTE
WITH RankedOrders AS
(SELECT OrderId,
CustomerId,
OrderDate,
ROW_NUMBER() OVER (PARTITION BY CustomerId
ORDER BY OrderDate DESC) AS RowNumber
FROM Orders)
SELECT OrderId,
CustomerId,
OrderDate
FROM RankedOrders
WHERE RowNumber = 1;Using a Subquery
SELECT OrderId,
CustomerId,
OrderDate
FROM
(SELECT OrderId,
CustomerId,
OrderDate,
ROW_NUMBER() OVER (PARTITION BY CustomerId
ORDER BY OrderDate DESC) AS RowNumber
FROM Orders) AS RankedOrders
WHERE RowNumber = 1;Both versions produce the same result.
I generally prefer the CTE version where the query is reasonably complex because it is often easier to read and maintain.
Finding Duplicate Records
ROW_NUMBER() can also be used to find duplicate records.
Suppose a customer should only appear once for each email address, but duplicate records have been inserted.
WITH DuplicateCustomers AS
(SELECT CustomerId,
CustomerName,
EmailAddress,
ROW_NUMBER() OVER (PARTITION BY EmailAddress
ORDER BY CustomerId) AS RowNumber
FROM Customers)
SELECT CustomerId,
CustomerName,
EmailAddress,
RowNumber
FROM DuplicateCustomers
WHERE RowNumber > 1;The numbering restarts for each email address.
The first occurrence receives a row number of 1. Any additional record with the same email address receives a higher row number.
Filtering for RowNumber > 1 therefore returns the duplicate records.
Deleting Duplicate Records
The same approach can be used to delete duplicates while retaining one copy of each record.
WITH DuplicateCustomers AS
(SELECT CustomerId,
EmailAddress,
ROW_NUMBER() OVER (PARTITION BY EmailAddress
ORDER BY CustomerId) AS RowNumber
FROM Customers)
DELETE
FROM DuplicateCustomers
WHERE RowNumber > 1;
This keeps the customer with the lowest CustomerId and removes the additional records with the same email address.
Care should always be taken before running a delete statement like this.
Run the CTE with a SELECT first and check exactly which records will be removed.
WITH DuplicateCustomers AS
(SELECT CustomerId,
EmailAddress,
ROW_NUMBER() OVER (PARTITION BY EmailAddress
ORDER BY CustomerId) AS RowNumber
FROM Customers)
SELECT *
FROM DuplicateCustomers
WHERE RowNumber > 1;
Once the results have been checked, the SELECT can be replaced with the
DELETE.
Choosing Which Duplicate to Keep
The ORDER BY inside ROW_NUMBER() determines which record receives row number 1.
To keep the oldest record:
ROW_NUMBER() OVER (PARTITION BY EmailAddress
ORDER BY CreatedDate)To keep the newest record:
ROW_NUMBER() OVER (PARTITION BY EmailAddress
ORDER BY CreatedDate DESC)To keep the record with the highest identifier:
ROW_NUMBER() OVER (PARTITION BY EmailAddress
ORDER BY CustomerId DESC)
It is therefore important to think carefully about the ordering rather than simply adding
ORDER BY to make the query run.
Using ROW_NUMBER() for Paging
ROW_NUMBER() can be used to return a particular page of results.
For example, the following query returns rows 21 to 30:
WITH NumberedCustomers AS
(SELECT CustomerId,
CustomerName,
EmailAddress,
ROW_NUMBER() OVER (
ORDER BY CustomerName, CustomerId) AS RowNumber
FROM Customers)
SELECT CustomerId,
CustomerName,
EmailAddress
FROM NumberedCustomers
WHERE RowNumber BETWEEN 21 AND 30
ORDER BY RowNumber;This would represent page 3 when displaying 10 records per page.
The start and end row numbers can be calculated using the page number and page size.
DECLARE @ PageNumber int = 3;
DECLARE @ PageSize int = 10;
DECLARE @ StartRow int = ((@ PageNumber - 1) * @ PageSize) + 1;
DECLARE @ EndRow int = @ PageNumber * @ PageSize;
WITH NumberedCustomers AS
(SELECT CustomerId,
CustomerName,
EmailAddress,
ROW_NUMBER() OVER (
ORDER BY CustomerName, CustomerId) AS RowNumber
FROM Customers)
SELECT CustomerId,
CustomerName,
EmailAddress
FROM NumberedCustomers
WHERE RowNumber BETWEEN @ StartRow AND @ EndRow
ORDER BY RowNumber;
Modern versions of SQL Server also support OFFSET and FETCH for paging,
but ROW_NUMBER() remains useful when more control is required.
Ordering Must Be Deterministic
A common mistake is to order by a column that does not contain unique values.
ROW_NUMBER() OVER (
ORDER BY OrderDate)If several orders have the same date, SQL Server is not guaranteed to assign those rows the same numbers every time the query runs.
To make the ordering deterministic, add a unique column as a secondary sort:
ROW_NUMBER() OVER (
ORDER BY OrderDate, OrderId)This gives SQL Server a consistent way to decide the exact order of records that share the same date.
The same principle applies when selecting the latest record from each group:
ROW_NUMBER() OVER (PARTITION BY CustomerId
ORDER BY OrderDate DESC, OrderId DESC)
If two orders have the same date, the order with the highest OrderId will receive row number 1.
ROW_NUMBER() Versus RANK()
SQL Server also provides the RANK() and DENSE_RANK() functions.
Although they are similar to ROW_NUMBER(), they handle matching values differently.
SELECT EmployeeName,
Salary,
ROW_NUMBER() OVER (
ORDER BY Salary DESC) AS RowNumber,
RANK() OVER (
ORDER BY Salary DESC) AS SalaryRank,
DENSE_RANK() OVER (
ORDER BY Salary DESC) AS DenseSalaryRank
FROM Employees;The result could look like this:
EmployeeName Salary RowNumber SalaryRank DenseSalaryRank
-----------------------------------------------
Sarah 60000 1 1 1
David 55000 2 2 2
Jane 55000 3 2 2
Michael 50000 4 4 3ROW_NUMBER()always assigns a different number to every row.RANK()gives matching values the same rank but leaves gaps afterwards.DENSE_RANK()gives matching values the same rank without leaving gaps.
Use ROW_NUMBER() when every row must have its own unique sequence number.
ROW_NUMBER() Does Not Guarantee the Final Display Order
The ORDER BY inside the OVER clause controls how the row numbers are calculated.
It does not necessarily guarantee the order in which the final results are displayed.
SELECT ROW_NUMBER() OVER (
ORDER BY CustomerName) AS RowNumber,
CustomerId,
CustomerName
FROM Customers;SQL Server will usually appear to return these records in row-number order, but this should not be relied upon.
Add an ORDER BY to the outer query when the display order matters:
WITH NumberedCustomers AS
(SELECT ROW_NUMBER() OVER (
ORDER BY CustomerName, CustomerId) AS RowNumber,
CustomerId,
CustomerName
FROM Customers)
SELECT RowNumber,
CustomerId,
CustomerName
FROM NumberedCustomers
ORDER BY RowNumber;Using ROW_NUMBER() with Joins
ROW_NUMBER() can be used after joining several tables.
For example, the following query returns the most recent order for each customer:
WITH RankedOrders AS
(SELECT c.CustomerId,
c.CustomerName,
o.OrderId,
o.OrderDate,
o.TotalAmount,
ROW_NUMBER() OVER (PARTITION BY c.CustomerId
ORDER BY o.OrderDate DESC, o.OrderId DESC) AS RowNumber
FROM Customers AS c
INNER JOIN Orders AS o ON o.CustomerId = c.CustomerId)
SELECT CustomerId,
CustomerName,
OrderId,
OrderDate,
TotalAmount
FROM RankedOrders
WHERE RowNumber = 1;The join is performed first, and the resulting order records are then numbered separately for each customer.
Returning the Top Three Records per Group
You are not limited to selecting only row number 1.
For example, the following query returns the three most recent orders for every customer:
WITH RankedOrders AS
(SELECT OrderId,
CustomerId,
OrderDate,
TotalAmount,
ROW_NUMBER() OVER (PARTITION BY CustomerId
ORDER BY OrderDate DESC, OrderId DESC) AS RowNumber
FROM Orders)
SELECT OrderId,
CustomerId,
OrderDate,
TotalAmount
FROM RankedOrders
WHERE RowNumber <= 3
ORDER BY CustomerId,
RowNumber;This is often described as a “top N per group” query.
Performance Considerations
SQL Server normally needs to sort the relevant rows to calculate ROW_NUMBER().
On a large table, this can require significant memory and processing time.
An appropriate index can improve performance, particularly where the indexed columns match the
PARTITION BY and ORDER BY columns.
For example, this query:
ROW_NUMBER() OVER (PARTITION BY CustomerId
ORDER BY OrderDate DESC)may benefit from an index such as:
CREATE INDEX IX_Orders_CustomerId_OrderDate ON Orders (CustomerId, OrderDate DESC);
Additional columns required by the query can potentially be added using INCLUDE:
CREATE INDEX IX_Orders_CustomerId_OrderDate ON Orders (CustomerId, OrderDate DESC) INCLUDE (TotalAmount);Index requirements will depend on the complete query, the amount of data, and the other ways in which the table is used. The execution plan should be checked before adding an index solely for one query.
Common Mistakes
Trying to Filter the Alias in the Same Query
SELECT ROW_NUMBER() OVER (
ORDER BY OrderDate) AS RowNumber,
OrderId
FROM Orders
WHERE RowNumber = 1;
This will fail because the alias is not available to the WHERE clause.
Use a CTE or subquery instead.
Forgetting PARTITION BY
ROW_NUMBER() OVER (
ORDER BY OrderDate DESC)This numbers every order in one sequence.
To number orders separately for each customer, include:
ROW_NUMBER() OVER (PARTITION BY CustomerId
ORDER BY OrderDate DESC)Using Non-Deterministic Ordering
ROW_NUMBER() OVER (PARTITION BY CustomerId
ORDER BY OrderDate DESC)If two records have the same order date, either record could be assigned row number 1.
Add a unique tie-breaker:
ROW_NUMBER() OVER (PARTITION BY CustomerId
ORDER BY OrderDate DESC, OrderId DESC)Assuming ROW_NUMBER() Is Stored
The generated row number only exists for the duration of the query. It does not update the table or create a permanent sequence number.
Where a permanent sequential identifier is required, an IDENTITY column or a
SQL Server sequence object may be more appropriate.
Summary
ROW_NUMBER() assigns a unique sequential number to every row within a result set or group.
Its general syntax is:
ROW_NUMBER() OVER (PARTITION BY GroupingColumn
ORDER BY SortingColumn)
The PARTITION BY clause is optional. When it is included, the numbering restarts from 1 for each group.
Common uses include:
- Finding the latest record for each customer, order, or other group.
- Identifying and removing duplicate records.
- Returning the top number of records from each group.
- Adding sequential numbers to query results.
- Implementing paging.
The most important point is that the ORDER BY inside the
OVER clause determines which row receives each number.
Where the ordering columns are not unique, an additional tie-breaker should be included to make the result predictable.