SQL Server 2005 adds the functionality of a Ranking expression that can be added to your resultset that is based on a ranking algorithm being applied to a column that you specify. This will come in handy in .NET applications for paging and sorting in a grid as well as many other scenarios.
The most basic new ranking function is ROW_NUMBER(). ROW_NUMBER() returns a column as an expression that contains the row’s number in the result set. This is only a number used in the context of the resultset, if the result changes, the ROW_NUMBER() will change. The ROW_NUMBER() expression takes an ORDER BY statement with the column you want to use for the row count with an OVER operator as shown here:
Select SalesOrderID, CustomerID,
Row_Number() Over (Order By SalesOrderID) as RunningCount
From Sales.SalesOrderHeader
Where SalesOrderID>10000
Order By SalesOrderID
Results are shown here:
SalesOrderID CustomerID RunningCount
------------ ----------- --------------------
43659 676 1
43660 117 2
43661 442 3
43662 227 4
43663 510 5
43664 397 6
43665 146 7
43666 511 8
43667 646 9
...More
Alternatively if you have an ORDER BY clause in your result set different than your ORDER BY in your ROW_NUMBER() expression
--Row_Number using a unique value, different order by
Select SalesOrderID, CustomerID,
Row_Number() Over (Order By SalesOrderID) as RunningCount
From Sales.SalesOrderHeader
Where SalesOrderID>10000
Order By CustomerID --Different ORDER BY than in Row_NUMBER
The result is shown here:
SalesOrderID CustomerID RunningCount
------------ ----------- --------------------
43860 1 202
44501 1 843
45283 1 1625
46042 1 2384
46976 2 3318
47997 2 4339
49054 2 5396
...More
If you choose the ROW_NUMBER() function to run against a non-unique column, it will break the tie and still produce a running count so no rows will have the same number. For example, CUSTOMERID can repeat in this example and there will be several ties, SQL Server will just produce a monotonically increasing number, which means nothing other than the number in the result set as shown here:
Select SalesOrderID, CustomerID,
Row_Number() Over (Order By CustomerID) as RunningCount
From Sales.SalesOrderHeader
Where SalesOrderID>10000
Order By CustomerID
The result are shown here:
SalesOrderID CustomerID RunningCount
------------ ----------- --------------------
43860 1 1
44501 1 2
45283 1 3
46042 1 4
46976 2 5
47997 2 6
49054 2 7
50216 2 8
51728 2 9
57044 2 10
63198 2 11
69488 2 12
44124 3 13
. . . More
Tomorrow RANK()...