NTILE

NTILE

Distributes the rows in an ordered partition into a specified number of groups.

Syntax

NTILE (integer_expression) OVER ( [ <partition_by_clause> ] < order_by_clause > )

integer_expression - Is number of groups desired.
partition_by_clause - Divides result set

Note : If number of partition is divisible by number of groups, rows will be evenly distributed.
Suppose, number of rows=53 and number of groups is 5, first 3 group will have 11 each and rest 2 will have 10 each.

Example

SELECT e.LastName, 
NTILE(4) OVER(ORDER BY SUM(SalesAmountQuota) DESC) AS Quartile, 
CONVERT (varchar(13), SUM(SalesAmountQuota), 1) AS SalesQuota 
FROM dbo.DimEmployee AS e 
INNER JOIN dbo.FactSalesQuota AS sq 
ON e.EmployeeKey = sq.EmployeeKey 
WHERE sq.CalendarYear = 2003 
AND SalesTerritoryKey IS NOT NULL 
AND SalesAmountQuota <> 0 
GROUP BY e.LastName 
ORDER BY Quartile, e.LastName;