The NTILE TSQL Function

July 27th, 2011 by Rhys

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.

?View Code TSQL
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.

e09902c5b352aeecd857615d966b86c1.124.88 The NTILE TSQL Function

Posted in T-SQL | No comments

Previous post:
Next post:

Leave a comment