Finding gaps-and-islands with repeating group numbers

Question

We have a MS-SQL dataset of tests run in various groupings and need to uniquely identify the groups

• sr is the device identifier
• tx is the TrackingIndex (insert order)
• id is the loop order of each grouped sr

The table looks like:

sr   |tx     |id
-----+-------+---
0027 |9748116|  1
0027 |9748117|  1
0027 |9751933|  1
0027 |9751934|  1
0027 |9751935|  1
01672|9813195|  1
01672|9813196|  2
01672|9813197|  3
01767|9794370|  1
01767|9794374|  2
01767|9794378|  3
01767|9794382|  4
01767|9794386|  5
01767|9813181|  1
01767|9813182|  1

I would like to see a column added like:

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

I have been trying to use the Island method, but am not able to create the above column. Most solutions expect the "id" number to be one continuous count with holes. Ours should always start at one.

SELECT *, ROW_NUMBER() OVER (ORDER BY tx) - id AS grp
FROM S

In the long run I may just change our insert to create a grouped column. Regardless, I will have to recreate it for all existing entries.

Answer 1

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