sql - Group rows into sets of 5

Question

TableA

Col1
----------
1
2
3
4....all the way to 27

I want to add a second column that assigns a number to groups of 5.

Results

Col1         Col2
-----        ------
1            1
2            1
3            1
4            1
5            1
6            2
7            2
8            2...and so on

The 6th group should have 2 rows in it.

NTILE doesn't accomplish what I want because of the way NTILE handles the groups if they aren't divisible by the integer.

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.

This is clearly demonstrated in this SQL Fiddle. Groups 4, 5, 6 each have 4 rows while the rest have 5. I have some started some solutions but they were getting lengthy and I feel like I'm missing something and that this could be done in a single line.

Answer 1

You can use this:

;WITH CTE AS
(
    SELECT col1,
           RN = ROW_NUMBER() OVER(ORDER BY col1)
    FROM TableA
)
SELECT col1, (RN-1)/5+1 col2
FROM CTE;

In your sample data, col1 is a correlative without gaps, so you could use it directly (if it's an INT) without using ROW_NUMBER(). But in the case that it isn't, then this answer works too. Here is the modified sqlfiddle.