:::: MENU ::::

SQL Server 2005 ranking function NTILE(n)

  • Nov 11 / 2008
  • 0
dbDigger, Ranking Functions, T-SQL Enhancements

SQL Server 2005 ranking function NTILE(n)

SQL Server 2005 ranking function NTILE(n) distributes the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. If the number of rows in a partition is not divisible by integer_expression, this will cause groups of two sizes that differ by one member. Larger groups come before smaller groups in the order specified by the OVER clause. For each row, NTILE returns the number of the group to which the row belongs. Return type of NTILE(n) is bigint. Syntax for NTILE(n) is

 
NTILE (integer_expression) OVER ( [ ] <> )  

Integer_expression is a positive integer constant expression that specifies the number of groups into which each partition must be divided. integer_expression can be of type int, or bigint.
It divides the result set produced by the FROM clause into partitions to which the RANK function is applied.<> determines the order in which the NTILE values are assigned to the rows in a partition. The following example of NTILE() from BOL divides the rows into four groups. Because the total number of rows is not divisible by the number of groups, the first group has four rows and the remaining groups have three rows each.

 
USE AdventureWorks;  
 GO  
 SELECT c.FirstName, c.LastName  
 ,NTILE(4) OVER(ORDER BY SalesYTD DESC) AS 'Quartile'  
 ,s.SalesYTD, a.PostalCode  
 FROM Sales.SalesPerson s  
 INNER JOIN Person.Contact c  
 ON s.SalesPersonID = c.ContactID  
 INNER JOIN Person.Address a  
 ON a.AddressID = c.ContactID  
 WHERE TerritoryID IS NOT NULL  
 AND SalesYTD &amp;lt;&amp;gt; 0  
 GO  

The following example of NTILE() adds the PARTITION BY argument to the code in example A. The rows are first partitioned by PostalCode and then divided into four groups within each PostalCode. Notice that the ORDER BY in the OVER clause orders the NTILE and the ORDER BY of the SELECT statement orders the result set.

 
USE AdventureWorks;  
 GO  
 SELECT c.FirstName, c.LastName  
 ,NTILE(4) OVER(PARTITION BY PostalCode ORDER BY SalesYTD DESC) AS 'Quartile'  
 ,s.SalesYTD, a.PostalCode  
 FROM Sales.SalesPerson s  
 INNER JOIN Person.Contact c  
 ON s.SalesPersonID = c.ContactID  
 INNER JOIN Person.Address a  
 ON a.AddressID = c.ContactID  
 WHERE TerritoryID IS NOT NULL  
 AND SalesYTD &amp;lt;&amp;gt; 0  
 ORDER BY LastName  
 GO  
Consult us to explore the Databases. Contact us