Group rows into groups of 5
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 lines.
NTILE doesn't do what I want because of the way NTILE handles groups if they are not divisible by integers.
If the number of rows in a section is not divisible by integer_expression, this will cause groups of two sizes to differ by one element. Large groups refer to smaller groups in the order specified in 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 remaining two groups will have 10 rows. If, on the other hand, the total number of rows is divided 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 have 4 lines and the other 5. I have some solutions but they are getting long and I feel like I am missing something and that it can be done in one line.
source to share
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 data, the sample col1
is correlative with no spaces, so you can use it directly (if it is INT
) without using it ROW_NUMBER()
. But in case it doesn't, then this answer works too. Here's the modified sqlfiddle.
source to share
A little math can go a long way. subtracting 1 from all values puts 5s (edge cases) in the previous group here and 6 in the next. splitting the floors according to the size of your group and adding one gives the result you are looking for. Also, the SQLFiddle example captures your iterative insert - the table has only grown to 27.
SELECT col1,
floor((col1-1)/5)+1 as grpNum
FROM tableA
source to share