ROW_NUMBER

Numbers the output of a result set. More specifically, returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

ROW_NUMBER and RANK are similar. ROW_NUMBER numbers all rows sequentially (for example 1, 2, 3, 4, 5). RANK provides the same numeric value for ties (for example 1, 2, 2, 4, 5).

Syntax

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

Arguments

PARTITION BY value_expression

Divides the result set produced by the FROM clause into partitions to which the ROW_NUMBER function is applied. value_expression specifies the column by which the result set is partitioned. If PARTITION BY is not specified, the function treats all rows of the query result set as a single group.

order_by_clause

The ORDER BY clause determines the sequence in which the rows are assigned their unique ROW_NUMBER within a specified partition. It is required.

Return types

bigint

Example

SELECT ROW_NUMBER() OVER(PARTITION BY [IntColumn] ORDER BY [IntColumn]) AS [rno]
  FROM [IntTable]

See Also