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!
Leave a Reply