NTILE Function in SQL Server

There are 4 ranking functions in SQL Server. In this article we need to see an in-depth demonstration and examples using NTILE functions and how SQL Server is calculated using NTILE.

My previous article is Ranking Function in SQL Server.

ROW_NUMBER() OVER ([PARTITION BY CLAUSE] <ORDER BY CLUASE>)
RANK() OVER ([PARTITION BY CLAUSE] <ORDER BY CLUASE >)
DENSE_RANK() OVER ([PARTITION BY CLAUSE] <ORDER BY CLUASE >)
NTILE(INTEGER_EXPRESSION) OVER ([PARTITION BY CLAUSE] <ORDER BY CLUASE >):

The NTILE function in SQL Server is a ranking function that arranges the rows based on the requested ties that can be a number.

Example

CREATE DATABASE DEMOS
Use DEMOS


--CREATE PURCHASE TABLE
CREATE TABLE DBO.PURCHASE
(
    PROD_ID INT,
    PURCHASE_YEAR INT,
    PURCHASE_AMOUNT INT
)


--INSERT DATA TO PURCHASE
INSERT INTO DBO.PURCHASE (PROD_ID, PURCHASE_YEAR,PURCHASE_AMOUNT) VALUES(1,2009,10000)
INSERT INTO DBO.PURCHASE (PROD_ID, PURCHASE_YEAR,PURCHASE_AMOUNT) VALUES(1,2010,9000)
INSERT INTO DBO.PURCHASE (PROD_ID, PURCHASE_YEAR,PURCHASE_AMOUNT) VALUES(1,2011,8000)
INSERT INTO DBO.PURCHASE (PROD_ID, PURCHASE_YEAR,PURCHASE_AMOUNT) VALUES(1,2012,7000)
INSERT INTO DBO.PURCHASE (PROD_ID, PURCHASE_YEAR,PURCHASE_AMOUNT) VALUES(1,2013,14000)
INSERT INTO DBO.PURCHASE (PROD_ID, PURCHASE_YEAR,PURCHASE_AMOUNT) VALUES(1,2014,18000)
INSERT INTO DBO.PURCHASE (PROD_ID, PURCHASE_YEAR,PURCHASE_AMOUNT) VALUES(1,2015,15000)
INSERT INTO DBO.PURCHASE (PROD_ID, PURCHASE_YEAR,PURCHASE_AMOUNT) VALUES(2,2013,12000)
INSERT INTO DBO.PURCHASE (PROD_ID, PURCHASE_YEAR,PURCHASE_AMOUNT) VALUES(2,2014,8000)
INSERT INTO DBO.PURCHASE (PROD_ID, PURCHASE_YEAR,PURCHASE_AMOUNT) VALUES(2,2015,16000)
INSERT INTO DBO.PURCHASE (PROD_ID, PURCHASE_YEAR,PURCHASE_AMOUNT) VALUES(3,2012,7000)
INSERT INTO DBO.PURCHASE (PROD_ID, PURCHASE_YEAR,PURCHASE_AMOUNT) VALUES(3,2013,8000)
INSERT INTO DBO.PURCHASE (PROD_ID, PURCHASE_YEAR,PURCHASE_AMOUNT) VALUES(3,2014,9700)
INSERT INTO DBO.PURCHASE (PROD_ID, PURCHASE_YEAR,PURCHASE_AMOUNT) VALUES(3,2015,12500)

Select data

SELECT * FROM DBO.PURCHASE

Use NTILE function

SELECT *, NTILE (5) OVER (ORDER BY PROD_ID, PURCHASE_YEAR ASC) AS [EQUAL_TIES] FROM DBO.PURCHASE

In the preceding query result, a total of 14 rows can be equally distributed, 5 equal ties, 2 rows per 1 tie, reaming 4 rows can be distributed each tile per 1 row based on an order by clause.

SELECT *, NTILE (5) OVER (PARTITION BY PROD_ID ORDER BY PROD_ID, PURCHASE_YEAR ASC) AS [EQUAL_TIES] FROM DBO.PURCHASE

We can use the NTILE function with the Partition by clause.