SQL Server ROW_NUMBER() Function

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 is a temporary value calculated when the query is run. We can say ROW_NUMBER() is nondeterministic.

There is no guarantee that the rows returned by a query using ROW_NUMBER() will be ordered exactly the same with each execution unless the following conditions are true.

• Values of the partitioned column are unique.
• Values of the ORDER BY columns are unique.
• Combinations of values of the partition column and ORDER BY columns are unique.

Syntax:

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

Adding PARTITION BY clause ROW_NUMBER() value restart the numbering when value_expression value changes.

Example:

Table: SalesPerson

FirstName	LastName	TerritoryName
Lynn	Tsoflias	Australia
José	Saraiva	Canada
Garrett	Vargas	Canada
Jillian	Carson	Central
Ranjit	Varkey	France
Rachel	Valdez	Germany
Michael	Blythe	Northeast
Tete	Mensa-Annan	Northwest
David	Campbell	Northwest
Pamela	Ansman-Wolfe	Northwest
Tsvi	Reiter	Southeast
Linda	Mitchell	Southwest
Shu	Ito	Southwest
Jae	Pak	United Kingdom

Returning Row Number:

SELECT ROW_NUMBER() OVER(ORDER BY TerritoryName) AS Row,   
    FirstName, LastName,TerritoryName
FROM SalesPerson

Output:

ROW	FirstName	LastName	TerritoryName
1	Lynn	Tsoflias	Australia
2	Garrett	Vargas	Canada
3	José	Saraiva	Canada
4	Jillian	Carson	Central
5	Ranjit	Varkey	France
6	Rachel	Valdez	Germany
7	Michael	Blythe	Northeast
8	Tete	Mensa-Annan	Northwest
9	David	Campbell	Northwest
10	Pamela	Ansman-Wolfe	Northwest
11	Tsvi	Reiter	Southeast
12	Linda	Mitchell	Southwest
13	Shu	Ito	Southwest
14	Jae	Pak	United Kingdom

Using ROW_NUMBER() with PARTITION

SELECT ROW_NUMBER() OVER(PARTITION BY TerritoryName ORDER BY TerritoryName) AS Row,   
    FirstName, LastName,TerritoryName
FROM SalesPerson

Output:

ROW	FirstName	LastName	TerritoryName
1	Lynn	Tsoflias	Australia
1	Garrett	Vargas	Canada
2	José	Saraiva	Canada
1	Jillian	Carson	Central
1	Ranjit	Varkey	France
1	Rachel	Valdez	Germany
1	Michael	Blythe	Northeast
1	Tete	Mensa-Annan	Northwest
2	David	Campbell	Northwest
3	Pamela	Ansman-Wolfe	Northwest
1	Tsvi	Reiter	Southeast
1	Linda	Mitchell	Southwest
2	Shu	Ito	Southwest
1	Jae	Pak	United Kingdom