skip to main content
Functions : Scalar functions : ROW_NUMBER
 

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:
 
Row Number
FirstName
Age
1
2
3
4
5
6
7
8
9
10
11
Larry
Doris
George
Mary
Sherry
Sam
Ted
Marty
Sue
Frank
John
5
6
6
11
11
17
23
23
29
38
40
 
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:
 
Row Number by Record Set
FirstName
Age
1
2
3
4
5
6
7
8
9
10
11
Ted
John
George
Mary
Sam
Doris
Frank
Larry
Sue
Sherry
Marty
23
40
6
11
17
6
38
5
29
11
23
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:
 
Partition by Gender
FirstName
Age
Gender
1
2
3
4
1
2
3
4
5
6
7
Doris
Mary
Sherry
Sue
Larry
George
Sam
Ted
Marty
Frank
John
23
40
6
11
17
6
38
5
29
11
23
F
F
F
F
M
M
M
M
M
M
M
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.