SQL-Server - Select sequentially into groups then start over but keep grouped id's together-CodePudding

I need to assign a value to each row sequentially up to a number, let's say 7, then start over at 1. The only catch is that if the id's are the same for two rows they need to stay together.

The first two columns are my data and the third is what I'd like to assign:

Individual_ID	Site_ID	Code_Assignment
0001	0070000036	1
0001	0090000028	2
0001	0100000002	3
0002	0100000002	3
0001	0100000004	5
0001	0100000009	6
0002	0100000009	6
0003	0100000009	6
0001	0100000029	7
0002	0100000029	7
0003	0100000029	7
0001	0100000030	1
0002	0100000030	1
0001	0100000032	2
0002	0100000032	2
0003	0100000032	2
0001	0100000033	3
0001	0100000036	4
0002	0100000036	4
0001	0100000040	5
0002	0100000040	5
0001	0100000044	6
0002	0100000044	6
0001	0100000045	7
0002	0100000045	7
0001	0100000046	1
0002	0100000046	1

CodePudding user response：

You can use DENSE_RANK() to compute the number. For example:

select t.*,
  (dense_rank() over(order by site_id) - 1) % 7   1 as code_assignement
from t

Result:

 id  site_id     code_assignement 
 --- ----------- ---------------- 
 1   0070000036  1                
 1   0090000028  2                
 1   0100000002  3                
 2   0100000002  3                
 1   0100000004  4                
 1   0100000009  5                
 2   0100000009  5                
 3   0100000009  5                
 1   0100000029  6                
 2   0100000029  6                
 3   0100000029  6                
 1   0100000030  7                
 2   0100000030  7                
 1   0100000032  1                
 2   0100000032  1                
 3   0100000032  1

It seems your example is not correct for rows with value 0100000002; they should produce the same code, not different ones.

See example at db<>fiddle.