TSQL Queries – Using NTILE

NTILE is a SQL Server function that can be called in a TSQL Query. This is part of the free sql query training to prepare for the Microsoft 70-461 queries exam.
Here is a video presentation on using the TSQL NTILE function.

And here is the sample code associated with the presentation. Feel free to copy and paste to SSMS and run it yourself.

CREATE DATABASE [QueryTraining];
GO

USE [QueryTraining];



-- Table to be used by Over Clause Rows/Range
CREATE TABLE REVENUE
(
[DepartmentID] int,
[Revenue] int,
[Year] int
);

insert into REVENUE
values (1,10030,1998),(2,20000,1998),(3,40000,1998),
       (1,20000,1999),(2,60000,1999),(3,50000,1999),
       (1,40000,2000),(2,40000,2000),(3,60000,2000),
       (1,30000,2001),(2,30000,2001),(3,70000,2001),
       (1,90000,2002),(2,20000,2002),(3,80000,2002),
       (1,10300,2003),(2,1000,2003), (3,90000,2003),
       (1,10000,2004),(2,10000,2004),(3,10000,2004),
       (1,20000,2005),(2,20000,2005),(3,20000,2005),
       (1,40000,2006),(2,30000,2006),(3,30000,2006),
       (1,70000,2007),(2,40000,2007),(3,40000,2007),
       (1,50000,2008),(2,50000,2008),(3,50000,2008),
       (1,20000,2009),(2,60000,2009),(3,60000,2009),
       (1,30000,2010),(2,70000,2010),(3,70000,2010),
       (1,80000,2011),(2,80000,2011),(3,80000,2011),
       (1,10000,2012),(2,90000,2012),(3,90000,2012);



-- first lets look at the REVENUE table

SELECT * 
  FROM Revenue;



-- NTILE Function


-- Rank all the revenue amounts on a scale of 1 to 10
SELECT *, NTILE(10) OVER(ORDER BY [Revenue] ASC) 
  FROM Revenue
  ORDER BY [Year];

-- Rank all the revenue amounts on a scale of 1 to 100
SELECT *, NTILE(100) OVER(ORDER BY [Revenue] ASC) AS Percentile
  FROM Revenue
  ORDER BY [Year];
-- or does it.....


SELECT *, NTILE(20) OVER(ORDER BY [Revenue] DESC) AS NTile20 
  FROM Revenue
  ORDER BY NTile20;


-- multiple NTILES
SELECT *, 
	   NTILE(10) OVER(ORDER BY [Revenue] DESC) AS NTile10, 
	   NTILE(20) OVER(ORDER BY [Revenue] DESC) AS NTile20, 
	   NTILE(4) OVER(ORDER BY [Revenue] DESC) AS Quartile
  FROM Revenue
  ORDER BY NTile20;

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!