DENSE_RANK works exactly like RANK() but will remove the skipping of numbers in the tie.
Select SalesOrderID, CustomerID,
DENSE_RANK() Over (Order By CustomerID) as RunningCount
From Sales.SalesOrderHeader
Where SalesOrderID>10000
Order By CustomerID
SalesOrderID CustomerID RunningCount
------------ ----------- --------------------
43860 1 1
44501 1 1
45283 1 1
46042 1 1
46976 2 2
47997 2 2
49054 2 2
50216 2 2
51728 2 2
57044 2 2
63198 2 2
69488 2 2
44124 3 3
. . . More
NTile(n) will evenly divide all the results into approximately even pieces and assign each piece the same number in the resultset. A perfect example is the percent of 100 (like for an examination in University) or a percentile of runners in a road race.
Select SalesOrderID, CustomerID,
NTILE(10000) Over (Order By CustomerID) as RunningCount
From Sales.SalesOrderHeader
Where SalesOrderID>10000
Order By CustomerID
SalesOrderID CustomerID RunningCount
------------ ----------- --------------------
43860 1 1
44501 1 1
45283 1 1
46042 1 1
46976 2 2
47997 2 2
49054 2 2
50216 2 2
51728 2 3
57044 2 3
63198 2 3
69488 2 3
44124 3 4
. . . More
One last example will bring these all together in one SQL Statement and show the difference between all four ranking functions.
--Ranking All
use adventureworks
Select SalesOrderID as OrderID, CustomerID,
Row_Number() Over (Order By CustomerID) as RowNum,
RANK() Over (Order By CustomerID) as Rank,
DENSE_RANK() Over (Order By CustomerID) as DRank,
NTILE(10000) Over (Order By CustomerID) as NTile
From Sales.SalesOrderHeader
Where SalesOrderID>10000
Order By CustomerID
OrderID CustomerID RowNum Rank DRank NTile
----------- ----------- -------------------- -------------------- -------------------- --------------------
43860 1 1 1 1 1
44501 1 2 1 1 1
45283 1 3 1 1 1
46042 1 4 1 1 1
46976 2 5 5 2 2
47997 2 6 5 2 2
49054 2 7 5 2 2
50216 2 8 5 2 2
51728 2 9 5 2 3
57044 2 10 5 2 3
63198 2 11 5 2 3
69488 2 12 5 2 3
44124 3 13 13 3 4
44791 3 14 13 3 4
. . . More