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. 🙂
Categories: T-SQL
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
LikeLike
Thanks for your comment and link, Vipul.
LikeLike