The NTILE TSQL Function

The NTILE is used to assigned records into the desired number of groups. NTILE assigns a number to each record indicating the group it belongs to. The number of records in each group will be the same if possible, otherwise some groups will have less than the others.

This function may be useful for assigning a number of sales leads to a certain number of groups. Run the following TSQL in SSMS.

WITH MyCTE
(
	Company,
	Telephone
)
AS
(
	SELECT 'ACME LTD', '0123456789'
	UNION ALL
	SELECT 'Big Corp', '0987654321'
	UNION ALL
	SELECT 'Bobs Bits', '9999999999'
	UNION ALL
	SELECT 'Daves Hardware', '000000000000'
	UNION ALL
	SELECT 'Maestrosoft', '111111111111'
	UNION ALL
	SELECT 'Boracle Corp', '333333333333'
	UNION ALL
	SELECT 'White Dwarf Microsystems', '5555555555555'
	UNION ALL
	SELECT 'Big Telecom', '4444444444444'
	UNION ALL
	SELECT 'DOL', '666666666666'
	UNION ALL
	SELECT 'London Media Ltd', '888888888888'
)
SELECT Company,
	   Telephone,
	   NTILE(3) OVER(ORDER BY Company) AS [Group]
FROM MyCTE;

Note how there are four records in group 1 and the remaining groups get three each.