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 A database management system (DBMS) by Microsoft Corporation. SQL(Structured Query Language) is a database computer language designed for the retrieval and management of data in relational database management systems (RDBMS), database schema creation and modification, and database object access control management.SQL Server since 2008 version.
NTILE function returns the number of group to which the row of result set is to be assigned.
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.
Pay attention to blue color groups (B). The two first groups include 6 balloons whereas the third one includes only 5.
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