Jerry Nixon on Windows: SQL 2005 NTile() syntax is pretty easy, the results are powerful

Jerry Nixon on Windows

Friday, March 14, 2008

SQL 2005 NTile() syntax is pretty easy, the results are powerful

Here's some SQL syntax I have been meaning to post for the past three years! Ha! It's against Northwind.

/* 
NTile()

Distributes the rows in an ordered partition into a specified number of groups.
The groups are numbered, starting at one. For each row, NTILE returns the number
of the group to which the row belongs.
*/

Select
NTile(4) OVER (ORDER BY ContactName) [Tile]
,ContactName
From
Customers
Where
ContactTitle = 'Owner'

-- use NTile for "percentile" - fills one tile at a time, one record at a time

Select
NTile(10) OVER (ORDER BY ContactName) [Tile]
,ContactName
From
Customers
Where
ContactTitle = 'Owner'

-- ntile is based on full result set

Select TOP (20)
NTile(10) OVER (ORDER BY ContactName) [Tile]
,ContactName
From
Customers

GO