ROW_NUMBER()

This function assigns a unique sequential integer to each row within a partition of a result set, starting with a specified value for the first row in each partition.

Syntax


ROW_NUMBER() OVER (
    [PARTITION BY value_expression ,...n]
    ORDER BY clause
)

Arguments

PARTITION BY value_expression, ...n: Divides the rows in the result set into partitions to which the ROW_NUMBER() function is applied. If PARTITION BY is not specified, the function treats the entire result set as a single partition.
ORDER BY clause: Specifies the order of rows within each partition. This is required. The ORDER BY clause determines the sequence in which rows are numbered.

Return Type

BIGINT

Description

ROW_NUMBER() is a window function that assigns a unique, sequential integer to each row within its partition. The numbering starts at 1 for the first row in each partition. The value of ORDER BY in the OVER clause dictates the order in which the numbers are assigned. If the ORDER BY clause produces identical values for multiple rows, those rows will still receive unique row numbers, but their relative order might be non-deterministic unless additional ordering columns are specified.

ROW_NUMBER() is useful for selecting a specific row from a set of rows, such as the top N rows in a category, or for de-duplicating rows based on specific criteria.

Examples

Example 1: Basic Usage

Assign a row number to each employee based on their salary, from highest to lowest.


SELECT
    EmployeeID,
    FirstName,
    LastName,
    Salary,
    ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum
FROM
    Employees;

Example 2: Partitioning by Department

Assign a row number to each employee within their department, ordered by salary.


SELECT
    EmployeeID,
    FirstName,
    LastName,
    Department,
    Salary,
    ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS RowNumPerDepartment
FROM
    Employees;

Microsoft Learn