What is ROW_NUMBER()?

ROW_NUMBER() is a ranking window function 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. It is commonly used for tasks like pagination, finding the top N rows per group, or removing duplicates.

What is ROW_NUMBER()?

ROW_NUMBER() is an analytic function that returns a unique row number for each row within a specified window or partition. The numbering starts at 1 for the first row in each partition and increments by 1 for subsequent rows. The order in which the numbers are assigned is determined by the ORDER BY clause within the OVER() clause. Unlike RANK() or DENSE_RANK(), ROW_NUMBER() guarantees unique numbers even for rows with identical values in the ordering columns, ensuring no ties.

Syntax

ROW_NUMBER() OVER ([PARTITION BY column1, column2, ...] ORDER BY column_x [ASC|DESC], column_y [ASC|DESC], ...)

Parameters Explained

• PARTITION BY: This optional clause divides the result set into partitions (groups) to which the ROW_NUMBER() function is applied independently. If omitted, the entire result set is treated as a single partition.
• ORDER BY: This mandatory clause within the OVER() specifies the logical order of rows within each partition. The ROW_NUMBER() function assigns sequential integers based on this order.

Example

Consider a table named 'Sales' with 'Region' and 'SalesAmount' columns. We want to rank sales within each region.

CREATE TABLE Sales (
    SaleID INT PRIMARY KEY,
    Region VARCHAR(50),
    SalesPerson VARCHAR(50),
    SalesAmount DECIMAL(10, 2)
);

INSERT INTO Sales (SaleID, Region, SalesPerson, SalesAmount) VALUES
(1, 'East', 'Alice', 1000.00),
(2, 'West', 'Bob', 1200.00),
(3, 'East', 'Charlie', 1500.00),
(4, 'North', 'David', 800.00),
(5, 'West', 'Eve', 1200.00),
(6, 'East', 'Frank', 900.00),
(7, 'North', 'Grace', 1100.00),
(8, 'West', 'Heidi', 950.00);

To get a unique row number for all sales ordered by SalesAmount (globally):

SELECT
    SaleID,
    Region,
    SalesPerson,
    SalesAmount,
    ROW_NUMBER() OVER (ORDER BY SalesAmount DESC) AS GlobalRank
FROM Sales;

To get a unique row number for sales within each region, ordered by SalesAmount:

SELECT
    SaleID,
    Region,
    SalesPerson,
    SalesAmount,
    ROW_NUMBER() OVER (PARTITION BY Region ORDER BY SalesAmount DESC) AS RegionalRank
FROM Sales;

Common Use Cases

• Pagination: Retrieving a specific 'page' of results (e.g., rows 11-20).
• Top N per Group: Selecting the top N items or records for each category or group.
• Duplicate Removal: Identifying and keeping only the first occurrence of a row based on a specific ordering criterion.
• Identifying specific rows: Such as the latest entry for a particular ID.

Key Characteristics

• Unique Numbers: Always assigns a unique integer to each row within its partition, even if ordering values are identical.
• Deterministic: The result is deterministic if the ORDER BY clause within OVER() specifies a unique ordering for each row within a partition. If not, the order of rows with identical values might be arbitrary.
• No Ties: Unlike RANK() and DENSE_RANK(), ROW_NUMBER() does not assign the same rank to rows with equal values.
• Window Function: It operates on a set of rows defined by the OVER() clause, not on the entire table.