How do I use ROW_NUMBER()?
Mastering ROW_NUMBER(): Partitioning, Ordering, and Use Cases

Learn how to effectively use the ROW_NUMBER()
window function in SQL to assign sequential integers to rows within a result set, enabling advanced data analysis and manipulation.
The ROW_NUMBER()
window function is a powerful tool in SQL that assigns a unique, sequential integer to each row within a partition of a result set, starting from 1 for the first row in each partition. Unlike IDENTITY
columns or COUNT(*)
, ROW_NUMBER()
is dynamic and calculated at query execution time, making it incredibly flexible for various analytical and data manipulation tasks. This article will guide you through its syntax, common use cases, and best practices.
Understanding the Syntax of ROW_NUMBER()
The basic syntax of ROW_NUMBER()
involves two key clauses: PARTITION BY
and ORDER BY
. These clauses define how the rows are grouped and in what order the numbers are assigned.
ROW_NUMBER() OVER (
[PARTITION BY column1, column2, ...]
ORDER BY column_to_sort_by [ASC | DESC], ...
)
Basic syntax of the ROW_NUMBER() function.
PARTITION BY
: This optional clause divides the result set into partitions (groups).ROW_NUMBER()
is applied independently to each partition. IfPARTITION BY
is omitted, the entire result set is treated as a single partition.ORDER BY
: This mandatory clause defines the logical order of rows within each partition (or the entire result set if noPARTITION BY
is specified). TheROW_NUMBER()
is assigned based on this order.
ORDER BY
clause within OVER()
when using ROW_NUMBER()
. Without it, the order of row numbering is non-deterministic, leading to inconsistent results.Common Use Cases for ROW_NUMBER()
ROW_NUMBER()
is incredibly versatile and can solve many common SQL challenges. Here are some of the most frequent applications:
flowchart TD A[Start] --> B{Need to assign unique numbers to rows?} B -- Yes --> C[Consider ROW_NUMBER()] C --> D{Need to group rows before numbering?} D -- Yes --> E[Use PARTITION BY] D -- No --> F[Omit PARTITION BY] E --> G[Define ORDER BY for numbering sequence] F --> G G --> H{Common Use Cases} H --> H1[Deduplicate Data] H --> H2[Get Nth Row per Group] H --> H3[Pagination] H --> H4[Top N per Group] H1 --> I[End] H2 --> I H3 --> I H4 --> I
Decision flow for using ROW_NUMBER() and its common applications.
1. Deduplicating Data
One of the most common uses for ROW_NUMBER()
is to identify and remove duplicate rows, especially when 'duplicate' means having the same values across a specific set of columns. By assigning a row number within partitions defined by these columns, you can easily select only the first (or last) occurrence.
WITH CTE_Deduplicate AS (
SELECT
ColumnA, ColumnB, ColumnC,
ROW_NUMBER() OVER (PARTITION BY ColumnA, ColumnB ORDER BY ColumnC) as rn
FROM
YourTable
)
SELECT
ColumnA, ColumnB, ColumnC
FROM
CTE_Deduplicate
WHERE
rn = 1;
Using ROW_NUMBER() to select distinct rows based on ColumnA and ColumnB, prioritizing the lowest ColumnC value.
2. Retrieving the Nth Row per Group (e.g., Latest Record)
When you need to fetch a specific row (like the most recent, oldest, or highest-valued) for each group, ROW_NUMBER()
combined with PARTITION BY
is ideal. For instance, getting the latest order for each customer.
WITH CustomerOrdersRanked AS (
SELECT
CustomerID, OrderID, OrderDate, TotalAmount,
ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC, OrderID DESC) as rn
FROM
Orders
)
SELECT
CustomerID, OrderID, OrderDate, TotalAmount
FROM
CustomerOrdersRanked
WHERE
rn = 1;
Fetching the latest order for each customer, breaking ties by OrderID.
3. Implementing Pagination
For displaying large datasets in smaller chunks (pages) in applications, ROW_NUMBER()
is a fundamental technique. You can assign a row number to the entire result set and then filter for a specific range.
DECLARE @PageNumber INT = 2;
DECLARE @PageSize INT = 10;
WITH PaginatedResults AS (
SELECT
ProductID, ProductName, Price,
ROW_NUMBER() OVER (ORDER BY ProductID) as rn
FROM
Products
)
SELECT
ProductID, ProductName, Price
FROM
PaginatedResults
WHERE
rn BETWEEN ((@PageNumber - 1) * @PageSize + 1) AND (@PageNumber * @PageSize);
SQL query for pagination using ROW_NUMBER() to retrieve the second page of results, with 10 items per page.
ROW_NUMBER()
works for pagination, modern SQL versions (e.g., SQL Server 2012+, PostgreSQL 8.4+, MySQL 8.0+) often offer OFFSET FETCH
clauses which are generally more efficient and concise for simple pagination scenarios.4. Top N Records per Group
Similar to retrieving the Nth row, ROW_NUMBER()
can easily fetch the top N records for each category or group. For example, finding the top 3 highest-selling products in each product category.
WITH ProductSalesRanked AS (
SELECT
CategoryID, ProductName, SalesAmount,
ROW_NUMBER() OVER (PARTITION BY CategoryID ORDER BY SalesAmount DESC) as rn
FROM
ProductSales
)
SELECT
CategoryID, ProductName, SalesAmount
FROM
ProductSalesRanked
WHERE
rn <= 3;
Finding the top 3 products by sales amount for each product category.