Just saw something interesting....we have a requirement to divide a result set into a certain number of groups that have a certain size. This size is controlled by a configuration setting, so the user expects to see groups of At first glance I thought the NTILE function would be the perfect match for this, until I tried using it. The problem is that if the number of rows don't evenly divide into the frame size, then you have 2 options:

Option #1: Honor the frame size for all the frames except the last one. Make the last frame smaller to handle the amount left over in the division.

Option #2: Attempt to make all the frames as evenly sized as possible.

So, if you have a result set of 101 rows and a frame size of 10, you would get the following:

Option #1: 10 frames of 10 rows and an 11th frame of 1 row.

Option #2: 2 frames of 10 rows and 9 frames of 9 rows.

It turns out that NTILE only supports option #2, and there is no setting to change that. From BOL: "If the number of rows in a partition is not divisible by integer_expression, this will cause groups of two sizes that differ by one member. Larger groups come before smaller groups in the order specified by the OVER clause. For example if the total number of rows is 53 and the number of groups is five, the first three groups will have 11 rows and the two remaining groups will have 10 rows each. If on the other hand the total number of rows is divisible by the number of groups, the rows will be evenly distributed among the groups. For example, if the total number of rows is 50, and there are five groups, each bucket will contain 10 rows."

I wonder if anyone has thought of proposing an option for NTILE to handle option #1? I didn't see anything in Microsoft Connect. SB