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.

  1. ROW_NUMBER() OVER ([PARTITION BY CLAUSE] <ORDER BY CLUASE>)  
  2. RANK() OVER ([PARTITION BY CLAUSE] <ORDER BY CLUASE >)  
  3. DENSE_RANK() OVER ([PARTITION BY CLAUSE] <ORDER BY CLUASE >)  
  4. 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
  1. CREATE DATABASE DEMOS  
  2. Use DEMOS  
  3.   
  4.   
  5. --CREATE PURCHASE TABLE  
  6. CREATE TABLE DBO.PURCHASE  
  7. (  
  8.     PROD_ID INT,  
  9.     PURCHASE_YEAR INT,  
  10.     PURCHASE_AMOUNT INT   
  11. )  
  12.   
  13.   
  14. --INSERT DATA TO PURCHASE  
  15. INSERT INTO DBO.PURCHASE (PROD_ID, PURCHASE_YEAR,PURCHASE_AMOUNT) VALUES(1,2009,10000)   
  16. INSERT INTO DBO.PURCHASE (PROD_ID, PURCHASE_YEAR,PURCHASE_AMOUNT) VALUES(1,2010,9000)  
  17. INSERT INTO DBO.PURCHASE (PROD_ID, PURCHASE_YEAR,PURCHASE_AMOUNT) VALUES(1,2011,8000)  
  18. INSERT INTO DBO.PURCHASE (PROD_ID, PURCHASE_YEAR,PURCHASE_AMOUNT) VALUES(1,2012,7000)  
  19. INSERT INTO DBO.PURCHASE (PROD_ID, PURCHASE_YEAR,PURCHASE_AMOUNT) VALUES(1,2013,14000)  
  20. INSERT INTO DBO.PURCHASE (PROD_ID, PURCHASE_YEAR,PURCHASE_AMOUNT) VALUES(1,2014,18000)  
  21. INSERT INTO DBO.PURCHASE (PROD_ID, PURCHASE_YEAR,PURCHASE_AMOUNT) VALUES(1,2015,15000)  
  22. INSERT INTO DBO.PURCHASE (PROD_ID, PURCHASE_YEAR,PURCHASE_AMOUNT) VALUES(2,2013,12000)  
  23. INSERT INTO DBO.PURCHASE (PROD_ID, PURCHASE_YEAR,PURCHASE_AMOUNT) VALUES(2,2014,8000)  
  24. INSERT INTO DBO.PURCHASE (PROD_ID, PURCHASE_YEAR,PURCHASE_AMOUNT) VALUES(2,2015,16000)  
  25. INSERT INTO DBO.PURCHASE (PROD_ID, PURCHASE_YEAR,PURCHASE_AMOUNT) VALUES(3,2012,7000)  
  26. INSERT INTO DBO.PURCHASE (PROD_ID, PURCHASE_YEAR,PURCHASE_AMOUNT) VALUES(3,2013,8000)  
  27. INSERT INTO DBO.PURCHASE (PROD_ID, PURCHASE_YEAR,PURCHASE_AMOUNT) VALUES(3,2014,9700)  
  28. INSERT INTO DBO.PURCHASE (PROD_ID, PURCHASE_YEAR,PURCHASE_AMOUNT) VALUES(3,2015,12500)  
Select data
  1. SELECT * FROM DBO.PURCHASE  
table

Use NTILE function
  1. SELECT *, NTILE (5) OVER (ORDER BY PROD_ID, PURCHASE_YEAR ASCAS [EQUAL_TIES] FROM DBO.PURCHASE  
use NTILE function

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.
  1. SELECT *, NTILE (5) OVER (PARTITION BY PROD_ID ORDER BY PROD_ID, PURCHASE_YEAR ASCAS [EQUAL_TIES] FROM DBO.PURCHASE  
We can use the NTILE function with the Partition by clause.

NTILE function with Partition by clause