Microsoft® SQL Server® 2012 Bible

264

Part II: Building Databases and Working with Data

5 through 10 get 7 rows each. This can skew the results for smaller data sets. For example,

15 rows into 10 tiles would place 10 rows in the lower 5 tiles and only place 5 tiles in the

upper 5 tiles. But for larger data sets — splitting a few hundred rows into 100 tiles, for

example — it works great.

This rule also applies if there are fewer rows than tiles. The rows are not spread across all

tiles; instead, the tiles are fi lled until the rows are consumed. For example, if fi ve rows are

split using NTILE(10), the result set would not use tiles 1, 3, 5, 7, and 9, but instead show

tiles 1, 2, 3, 4, and 5.

A common real-world example of NTILE() is the percentile scoring used in college entrance

exams.

The following query fi rst calculates the AdventureWorks products’ sales quantity in the

subquery. The outer query then uses the OVER() clause to sort by the sales count, and the

NTILE(100) to calculate the percentile according to the sales count:

USE AdventureWorks

GO

SELECT ProductID, SalesCount,

NTILE(100) OVER (ORDER BY SalesCount) as Percentile

FROM (SELECT ProductID, COUNT(*) as SalesCount

FROM Sales.SalesOrderDetail

GROUP BY ProductID

) AS Q

ORDER BY Percentile DESC;

Result (abbreviated):

ProductID SalesCount Percentile

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

712 3382 100

870 4688 100

921 3095 99

873 3354 99

707 3083 98

711 3090 98

922 2376 97

...

830 33 5

888 39 5

902 20 4

950 28 4

946 30 4

744 13 3

903 14 3

919 16 3

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

http://www.it-ebooks.info