Nathaniel Davenport Nathaniel Davenport - 3 months ago 19
SQL Question

Dynamic Parameter for NTILE SQL Function

I'm attempting to provide a grouping id to a set of items using NTILE(). Basically, every 4 items should be grouped together with the same GroupID. The problem is that the total number of rows is different per id. Is this possible?

SELECT
ProductDescription AS LabelType1,
NTILE(FLOOR(COUNT(bc.Groupings) / 4)) OVER (ORDER BY s.OrderId) AS GroupNumber,
Barcode AS Barcode1
FROM
dbo.table1 s
INNER JOIN
#BoxCounts bc ON s.OrderId = bc.OrderId

Answer

This is an elaboration on Ben Thul's comment (because he has not answered the question).

NTILE() divides a set of rows into n almost equal-sized groups. The n is a constant.

You want to assign a grouping id to a fixed number of rows. That is a different problem and easily handled with row_number() or rank().

So, one method is:

SELECT ProductDescription AS LabelType1,
       (ROW_NUMBER() OVER (ORDER BY s.OrderId) - 1) / 4 as GroupNumber,
       Barcode AS Barcode1
FROM dbo.table1 s INNER JOIN
     #BoxCounts bc
     ON s.OrderId = bc.OrderId;

Note the - 1 in the calculation, so the first group has four elements. Also, SQL Server does integer division, so you don't have to worry about additional decimal places.

If you could have ties and want all rows with the same OrderId to be in the same group, then use dense_rank() (if you want all groups to have four different order ids) or rank() (if you want all groups to have approximately four order ids).