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?
ProductDescription AS LabelType1,
NTILE(FLOOR(COUNT(bc.Groupings) / 4)) OVER (ORDER BY s.OrderId) AS GroupNumber,
Barcode AS Barcode1
#BoxCounts bc ON s.OrderId = bc.OrderId
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
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;
- 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).