Microsoft® SQL Server® 2012 Bible

263

Chapter 10: Aggregating, Windowing, and Ranking Data

10

USE AdventureWorks

GO

SELECT ProductID, SalesCount,

RANK() OVER (ORDER BY SalesCount) as [Rank],

DENSE_RANK() OVER(Order By SalesCount) as [DenseRank]

FROM (SELECT ProductID, COUNT(*) as SalesCount

FROM Sales.SalesOrderDetail

GROUP BY ProductID

) AS Q

ORDER BY [Rank];

Result (abbreviated):

ProductID SalesCount Rank DenseRank

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

897 2 1 1

942 5 2 2

943 6 3 3

911 6 3 3

927 9 5 4

898 9 5 4

744 13 7 5

903 14 8 6

...

This example perfectly demonstrates the difference between RANK() and DENSE_RANK().

RANK() counts each tie as a ranked row. In this example, Product IDs 943 and 911

both tie for third place but consume the third and fourth row in the ranking, placing

ProductID 927 in fi fth place.

DENSE_RANK() handles ties differently. Tied rows consume only a single value in the rank-

ing, so the next rank is the next place in the ranking order. No ranks are skipped. In the

previous query, ProductID 927 is in fourth place using DENSE_RANK().

Just as with the ROW_NUMBER() function, you can use RANK() and DENSE_RANK()with a

partitioned OVER() clause. You could partition the previous example by product category

to rank product sales with each category.

Ntile() Function

The fourth ranking function organizes the rows into n number of groups, called tiles, and

returns the tile number. For example, if the result set has 10 rows, then NTILE(5) would

split the 10 rows into fi ve equally sized tiles with 2 rows in each tile in the order of the

OVER() clause’s ORDER BY.

If the number of rows is not evenly divisible by the number of tiles, then the tiles get the

extra row. For example, for 74 rows and 10 tiles, the fi rst 4 tiles get 8 rows each, and tiles

c10.indd 263c10.indd 263 7/30/2012 4:26:14 PM7/30/2012 4:26:14 PM

http://www.it-ebooks.info