Nathaniel Davenport - 1 month ago 4x

SQL Question

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).

Source (Stackoverflow)

Comments