| RowNumber & Rank |
|
| RANK: |
The Rank expression sequentially number the rows in a result-set. Although most commonly used for
paging and sorting, the unique row number it returns is useful in various scenarios. |
| Syntax: |
ROWNUMBER ( ) OVER ( [ < partition_by_clause > ] < order_by_clause > )
RANK( ) OVER ( [ < partition_by_clause > ] < order_by_clause > ) |
| Arguments: |
ROWNUMBER ( ) : returns the incrementing row number
RANK( ): returns the incrementing row number with the exception that identical values in the Order By colunn are given the same ranking
<partition_by_clause> Divides the FROM clause result set into partitions to which the ranking is applied
<order_by_clause> The order the RANK values are applied to the rows in a partition |
| Sql Server Version: |
2005+ |
Refer to the Advanced ObjectDataSource Techniques to see how the rank expression is used for paging.
Note: This sample uses the AdventureWorks database which can be downloaded here |
Sample Row_Number Query:
Select lc.Name, lc.Availability, Row_Number()
Over (Order By Availability) as rowNum
From Production.Location lc
Results:
|
Name
|
Availability
|
rowNum
|
|
Tool Crib
|
0
|
1
|
|
Sheet Metal Racks
|
0
|
2
|
|
Paint Shop
|
0
|
3
|
|
Paint Storage
|
0
|
4
|
|
Metal Storage
|
0
|
5
|
|
Miscellaneous Storage
|
0
|
6
|
|
Finished Goods Storage
|
0
|
7
|
|
Specialized Paint
|
80
|
8
|
|
Frame Forming
|
96
|
9
|
|
Frame Welding
|
108
|
10
|
|
Debur and Polish
|
120
|
11
|
|
Paint
|
120
|
12
|
|
Subassembly
|
120
|
13
|
|
Final Assembly
|
120
|
14
|
Sample Rank() Query:
Select lc.Name, lc.Availability, Rank()
Over (Order By Availability) as rowNum
From Production.Location lc
Results:
Before:
|
Name
|
Availability
|
|
Tool Crib
|
0
|
|
Sheet Metal Racks
|
0
|
|
Paint Shop
|
0
|
|
Paint Storage
|
0
|
|
Metal Storage
|
0
|
|
Miscellaneous Storage
|
0
|
|
Finished Goods Storage
|
0
|
|
Frame Forming
|
96
|
|
Frame Welding
|
108
|
|
Debur and Polish
|
120
|
|
Paint
|
120
|
|
Specialized Paint
|
80
|
|
Subassembly
|
120
|
|
Final Assembly
|
120
|
After:
|
Name
|
Availability
|
rowNum
|
|
Tool Crib
|
0
|
1
|
|
Sheet Metal Racks
|
0
|
1
|
|
Paint Shop
|
0
|
1
|
|
Paint Storage
|
0
|
1
|
|
Metal Storage
|
0
|
1
|
|
Miscellaneous Storage
|
0
|
1
|
|
Finished Goods Storage
|
0
|
1
|
|
Specialized Paint
|
80
|
8
|
|
Frame Forming
|
96
|
9
|
|
Frame Welding
|
108
|
10
|
|
Debur and Polish
|
120
|
11
|
|
Paint
|
120
|
11
|
|
Subassembly
|
120
|
11
|
|
Final Assembly
|
120
|
11
|
|