NTILE in sql server

Today I will explain you regarding NTILE in sql server. I have already explained you  regarding  RANK(),  DENSE_RANK()  and ROW_NUMBER(). NTILE distributes the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. So in the below syntax if you have passe 3 at “integer_expression” then it will create 3 groups of your results.

Syntax : NTILE (integer_expression) OVER (PARTITION BY column_name ORDER BY column_name ASC/DESC)

“INTEGER_EXPRESSION” is a positive integer constant expression that specifies the number of groups into which each partition must be divided.
“PARTITION BY” divides the result set into partitions to which NTILE function is applied.

“ORDER BY” defines the order in which NTILE function is applied in a partitions applied above if any.

CREATE TABLE SALES
(
	ID INT IDENTITY(1,1) NOT NULL,
	FIRSTNAME VARCHAR(100) NOT NULL,
	LASTNAME VARCHAR(100) NOT NULL,
	PRODUCT VARCHAR(100) NOT NULL,
	PRICE DECIMAL(18,3) NOT NULL,
	COUNTRY VARCHAR(100) NOT NULL

)

INSERT INTO SALES(FIRSTNAME,LASTNAME,PRODUCT,PRICE,COUNTRY) VALUES('John','Elson','Samsung Galaxy Y',200.45,'USA')
INSERT INTO SALES(FIRSTNAME,LASTNAME,PRODUCT,PRICE,COUNTRY) VALUES('Rehman','Malik','I Phone',800.65,'Pakistan')
INSERT INTO SALES(FIRSTNAME,LASTNAME,PRODUCT,PRICE,COUNTRY) VALUES('Maulik','Jain','Samsung Galaxy Y',200.89,'India')
INSERT INTO SALES(FIRSTNAME,LASTNAME,PRODUCT,PRICE,COUNTRY) VALUES('Ching','Lee','HTC',700.87,'China')
INSERT INTO SALES(FIRSTNAME,LASTNAME,PRODUCT,PRICE,COUNTRY) VALUES('Kerry','White','Sony Vaio 15212',900.22,'USA')
INSERT INTO SALES(FIRSTNAME,LASTNAME,PRODUCT,PRICE,COUNTRY) VALUES('John','Elson','I Phone',600.23,'USA')
INSERT INTO SALES(FIRSTNAME,LASTNAME,PRODUCT,PRICE,COUNTRY) VALUES('John','Smith','Samsung S3',700.45,'USA')
INSERT INTO SALES(FIRSTNAME,LASTNAME,PRODUCT,PRICE,COUNTRY) VALUES('Thomas','Cook','HP Pavellion',1000.65,'USA')
INSERT INTO SALES(FIRSTNAME,LASTNAME,PRODUCT,PRICE,COUNTRY) VALUES('Lax','Patel','Micromax Canvas',500.97,'India')
INSERT INTO SALES(FIRSTNAME,LASTNAME,PRODUCT,PRICE,COUNTRY) VALUES('Sanjay','Rana','Nokia 800',400.44,'India')
INSERT INTO SALES(FIRSTNAME,LASTNAME,PRODUCT,PRICE,COUNTRY) VALUES('Nirav','Shah','Creative 630',50.23,'India')
INSERT INTO SALES(FIRSTNAME,LASTNAME,PRODUCT,PRICE,COUNTRY) VALUES('Tang','Bao','Toshiba',800.10,'China')
INSERT INTO SALES(FIRSTNAME,LASTNAME,PRODUCT,PRICE,COUNTRY) VALUES('Feng','Lee','HTC',450.41,'China')
INSERT INTO SALES(FIRSTNAME,LASTNAME,PRODUCT,PRICE,COUNTRY) VALUES('Adil','Rehman','Creative 630',45.68,'Pakistan')
INSERT INTO SALES(FIRSTNAME,LASTNAME,PRODUCT,PRICE,COUNTRY) VALUES('Ajmal','Hussain','Samsung Galaxy Y',200.89,'Pakistan')



SELECT 
NTILE(3) OVER (ORDER BY s.country) AS 'NTILE - 3',
NTILE(5) OVER (ORDER BY s.country) AS 'NTILE - 5',
s.country,s.FirstName, s.LastName
,s.product,s.price from SALES s

Have a look at the below screen shot where NTILE is applied twice on same number of records with different integer_expression. The table contains 15 records, so the NTILE(3) will divide the result set in 3 groups where each group will be allocated number starting with 1. So first five records will have 1, 5-10 have 2 and 10-15 have 3.

NTITLE

You can also visit my sqlfiddle at : http://sqlfiddle.com/#!3/fe5d8/31

Leave a Reply