The Coolness of the T-SQL NTILE Function

17 February, 2009 (21:47) | T-SQL | By: Mark V

I had need of calculating metrics across an industry and then returning the quartile in which each company scored. So, i wrote up a function that took in the MinValue, MaxValue, and CompanyMetricValue as inputs and returned the quartile for the Company. Great. Then, when asking  someone else about this using this new function, the response was, “Why not just use NTILE?” Ug.

Here is the description from SQL 2008 Books Online:

Distributes the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. For each row,  NTILE returns the number of the group to which the row belongs.

NTILE (integer_expression)    OVER ( [ <partition_by_clause> ] < order_by_clause > )

So, using NTILE, I was able to do the following:

SELECT NTILE(4) OVER(ORDER BY CompanyMetricValue ASC) AS Quartile

In the example above, i want quarters, so I use 4 as the (integer expression).

Lesson Learned: Before inventing a new wheel, check Books Online for wheels that already exist. :)

Comments

Pingback from Using Medians For Quartiling When NTILE Is Too Arbitrary « Mark V SQL
Time December 10, 2009 at 10:57 am

[…] in a previous post on The Coolness of the T-SQL NTILE Function, I discuss that NTILE can perform quartiling (or any other –tiling not requiring a trip to Home […]

Comment from vipul sachan
Time May 19, 2014 at 5:23 am

Nice post but i think should contain more detail, As refer to NTILE function a real time scenario can also be introduced –
If you works on HR management module in your project and you need to build a query in such a way that you should get Highest and medium salaried employee then it can be resolved by using NTILE rank function.

To get more detail check the below link –
http://vsstack.blogspot.in/p/ntile-function-in-sql-server.html

Comment from Mark V
Time May 26, 2014 at 9:46 pm

Thanks for your comment and link, Vipul.

Write a comment