Ranking Functions

-- Microsoft T-SQL ranking functions comparison

------------

-- MSSQL row_number (row number sequence) - NTILE

-- MSSQL rank and dense_rank functions - over order by clause

-- Data is rounded down to the thousands for ranking

USE AdventureWorks;

SELECT  

         c.AccountNumber                  AS CustAccount,

         FLOOR(h.SubTotal /1000)          AS [SubTotal (Thousands $)],

         ROW_NUMBER()

           OVER(ORDER BY FLOOR(h.SubTotal /1000) DESC) AS RowNumber,

         RANK()

           OVER(ORDER BY FLOOR(h.SubTotal /1000) DESC) AS Rank,

         DENSE_RANK()

           OVER(ORDER BY FLOOR(h.SubTotal /1000) DESC) AS DenseRank,

         NTILE(5)

           OVER(ORDER BY FLOOR(h.SubTotal /1000) DESC) AS NTile

FROM     Sales.Customer c

         INNER JOIN Sales.SalesOrderHeader h

           ON c.CustomerID = h.CustomerID

         INNER JOIN Sales.SalesTerritory t

           ON h.TerritoryID = t.TerritoryID

WHERE    t.Name = 'Germany'

  AND    YEAR(OrderDate) = 2004

  AND    SubTotal >= 4000.0

ORDER BY RowNumber;

GO

/* Results

CustAccount	SubTotal (Thousands $)	RowNumber	Rank	DenseRank	NTile
AW00000230	100	1	1	1	1
AW00000230	88	2	2	2	1
AW00000302	77	3	3	3	1
AW00000320	68	4	4	4	1
AW00000536	68	5	4	4	1
AW00000536	64	6	6	5	1
AW00000266	58	7	7	6	1
AW00000302	44	8	8	7	2
AW00000687	43	9	9	8	2
AW00000482	36	10	10	9	2
AW00000176	36	11	10	9	2