SQL Select statement split into groupings / chunks

Question

I am trying to do a select statement that is grouped into multiple 'chunks', defined by a user defined number (eg. 5, from lets say 12 rows)

What I would like to achieve, is a select statement that appends a column defining an incrementing group value of the defined chunk size, so for 12 rows, there would be rows 1-5 equal chunk 1, rows 6-10 equal chunk 2, rows 11-12 equal chunk 3.

I have tried searching on a few occassions for this solution with little success, I thought there would be a way to modify a basic ROW_NUMBER() OVER (ORDER BY id), that could reset the count after every 5 rows, and increase the group/chunk.

I realise there are other ways of achieving this result, but I am looking for the fastest simplest/cleanest solution. Idea is to apply this to a function that has to work out best case scenario grouping for batch submissions.

Example of desired output.

| Row no. | Item Id. | Chunk No. |
|--------:|:--------:|:---------:|
|    1    |   1001   |     1     |
|    2    |   1002   |     1     |
|    3    |   1003   |     1     |
|    4    |   1004   |     1     |
|    5    |   1005   |     1     |
|    6    |   1006   |     2     |
|    7    |   1007   |     2     |
|    8    |   1008   |     2     |
|    9    |   1009   |     2     |
|    10   |   1010   |     2     |
|    11   |   1011   |     3     |  
|    11   |   1012   |     3     |

Answer 1

You should just divide the RowNum to 5 for example with /

select ROW_NUMBER() over (ORDER BY id) RowNum,
       id,
       ((ROW_NUMBER() over (ORDER BY id) - 1)  / 5) +1 as ChunkNo  
from t

SQLFiddle demo

Answer 2

Just as another solution with custom labels:

SELECT 
ROW_NUMBER() over (ORDER BY ID) AS RowNum,
id,
  CASE
    WHEN ntile(3) over(ORDER BY ID)=1 THEN 'Label 1'
    WHEN ntile(3) over(ORDER BY ID)=2 THEN 'Label 2'
    WHEN ntile(3) over(ORDER BY ID)=3 THEN 'Label 3'
    ELSE 'OTHER'
  END AS My_Chunks
FROM t

or simple

SELECT 
 ROW_NUMBER() over (ORDER BY ID) AS RowNum,
 id,
 ntile(3) over(ORDER BY ID) AS My_Chunks
FROM t