NTILE function

The task. Distribute paint cans (balloons) among 3 groups. The groups are filling in order of increasing of v_id.

This task can be solved with aid of ranking function NTILE which is available in SQL Server since 2008 version.

NTILE function returns the number of group to which the row of result set is to be assigned.

select *, NTILE(3) OVER(order by v_id) gr from utv order by v_id;

🚫

[[ error ]]

[[ column ]]
NULL [[ value ]]

NTILE function has a parameter which represents group number. The more that could be said you know yet by now. :-)

If we’ll want to distribute the balloons of each color separately, we could use optional PARTITION BY element in the OVER clause, in the same manner as for other ranking functions.

select *, NTILE(3) OVER(PARTITION BY v_color order by v_id) gr
from utv order by v_color, v_id;

🚫

[[ error ]]

[[ column ]]
NULL [[ value ]]

Pay attention to blue color groups (B). The two first groups include 6 balloons whereas the third one includes only 5.
In the case where number of rows can’t be distributed equally, NTILE function puts in into the last groups by one row less.

At last, if argument of the NTILE function will be greater than number of rows, such a number of groups will be formed that equals to the number of rows, and each group will include only one row.

Suggested exercises: 130

#Ranking Functions #OVER #PARTITION BY #NTILE

RANK() and DENSE_RANK() functions