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
--Row_Number using a unique value, different order by
Order By CustomerID --Different ORDER BY than in Row_NUMBER
The result is shown here:
43860 1 202
44501 1 843
45283 1 1625
46042 1 2384
46976 2 3318
47997 2 4339
49054 2 5396
Row_Number() Over (Order By CustomerID) as RunningCount
Order By CustomerID
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()...
Page rendered at Tuesday, August 16, 2022 6:36:09 PM (Eastern Daylight Time, UTC-04:00)
Disclaimer The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.