Skip to main content
added 89 characters in body
Source Link
McNets
  • 23.9k
  • 11
  • 50
  • 89

I think next sentence returns your expected result.

Since id marks loop order for each group, you can SUM() all id's with a value of 1.

select   sr, tx, id, 
         sum(case when id = 1 then 1 else 0 end) over (order by sr, tx) as grp
from     test
order by sr, tx;
GO
sr    |      tx | id | grp
:---- | ------: | -: | --:
0027  | 9748116 |  1 |   1
0027  | 9748117 |  1 |   2
0027  | 9751933 |  1 |   3
0027  | 9751934 |  1 |   4
0027  | 9751935 |  1 |   5
01672 | 9813195 |  1 |   6
01672 | 9813196 |  2 |   6
01672 | 9813197 |  3 |   6
01767 | 9794370 |  1 |   7
01767 | 9794374 |  2 |   7
01767 | 9794378 |  3 |   7
01767 | 9794382 |  4 |   7
01767 | 9794386 |  5 |   7
01767 | 9813181 |  1 |   8
01767 | 9813182 |  1 |   9

dbfiddle here

I think next sentence returns your expected result.

select   sr, tx, id, 
         sum(case when id = 1 then 1 else 0 end) over (order by sr, tx) as grp
from     test
order by sr, tx;
GO
sr    |      tx | id | grp
:---- | ------: | -: | --:
0027  | 9748116 |  1 |   1
0027  | 9748117 |  1 |   2
0027  | 9751933 |  1 |   3
0027  | 9751934 |  1 |   4
0027  | 9751935 |  1 |   5
01672 | 9813195 |  1 |   6
01672 | 9813196 |  2 |   6
01672 | 9813197 |  3 |   6
01767 | 9794370 |  1 |   7
01767 | 9794374 |  2 |   7
01767 | 9794378 |  3 |   7
01767 | 9794382 |  4 |   7
01767 | 9794386 |  5 |   7
01767 | 9813181 |  1 |   8
01767 | 9813182 |  1 |   9

dbfiddle here

I think next sentence returns your expected result.

Since id marks loop order for each group, you can SUM() all id's with a value of 1.

select   sr, tx, id, 
         sum(case when id = 1 then 1 else 0 end) over (order by sr, tx) as grp
from     test
order by sr, tx;
GO
sr    |      tx | id | grp
:---- | ------: | -: | --:
0027  | 9748116 |  1 |   1
0027  | 9748117 |  1 |   2
0027  | 9751933 |  1 |   3
0027  | 9751934 |  1 |   4
0027  | 9751935 |  1 |   5
01672 | 9813195 |  1 |   6
01672 | 9813196 |  2 |   6
01672 | 9813197 |  3 |   6
01767 | 9794370 |  1 |   7
01767 | 9794374 |  2 |   7
01767 | 9794378 |  3 |   7
01767 | 9794382 |  4 |   7
01767 | 9794386 |  5 |   7
01767 | 9813181 |  1 |   8
01767 | 9813182 |  1 |   9

dbfiddle here

Source Link
McNets
  • 23.9k
  • 11
  • 50
  • 89

I think next sentence returns your expected result.

select   sr, tx, id, 
         sum(case when id = 1 then 1 else 0 end) over (order by sr, tx) as grp
from     test
order by sr, tx;
GO
sr    |      tx | id | grp
:---- | ------: | -: | --:
0027  | 9748116 |  1 |   1
0027  | 9748117 |  1 |   2
0027  | 9751933 |  1 |   3
0027  | 9751934 |  1 |   4
0027  | 9751935 |  1 |   5
01672 | 9813195 |  1 |   6
01672 | 9813196 |  2 |   6
01672 | 9813197 |  3 |   6
01767 | 9794370 |  1 |   7
01767 | 9794374 |  2 |   7
01767 | 9794378 |  3 |   7
01767 | 9794382 |  4 |   7
01767 | 9794386 |  5 |   7
01767 | 9813181 |  1 |   8
01767 | 9813182 |  1 |   9

dbfiddle here