ROW_NUMBER

Syntax

ROW_NUMBER() OVER([PARTITION BY value] ORDER BY value)

Description

This function returns the number of a rows in the result set, starting at 1 for the first row in each partition, if specified.

The OVER clause determines the partitioning and ordering of the rows in the result before the ROW_NUMBER function is applied using the following clauses:

• The PARTITION BY clause divides the result set produced by the FROM clause into partitions to which the ROW_NUMBER function is applied. This clause is optional.
• The ORDER BY clause determines the order in which ROW_NUMBER values are applied to the rows. An integer cannot represent a column in the ORDER BY clause.

value_expr must refer to columns made available by the FROM clause in the SELECT statement. It can be any of the following values:

• Column in the select list
• Scalar subquery
• Scalar function
• User-defined variable

It cannot refer to expressions or aliases.

Examples

Example A

SELECT ROW_NUMBER() OVER(ORDER BY Age) AS [Row Number by Age],
FirstName, Age
FROM Person

This statement returns the following results:

This example returns all rows, and sequentially numbers and orders them by Age.

Example B

SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS [Row Number by Record Set],
FirstName, Age 
FROM Person

This statement returns the following results:

This example sequentially numbers each row, but does not order them. Because the ROW_NUMBER function requires an ORDER BY clause, the ROW_NUMBER function specifies ORDER BY (SELECT 1) to return the rows in the order in which they are stored in the specified table and sequentially number them, starting from 1.

Example C

SELECT ROW_NUMBER() OVER(PARTITION BY Gender ORDER BY Age) AS [Partition By Gender], FirstName, Age, Gender FROM Person

This statement returns the following results:

This example sequentially numbers the groups of rows and orders them by Age (PARTITION BY Gender ORDER BY Age). Rows are sequentially numbered within each unique partition value. The Partition by Gender value sequence restarts at 1 for each new partition value in the result. For example, in this case, the Partition by Gender value sequence restarts at 1 for all rows that indicate an M gender.