# Finding gaps-and-islands with repeating group numbers

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.

• Please, tag your SQL Server version. Commented Feb 20, 2018 at 19:57

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

• That looks like it, Thanx Commented Feb 20, 2018 at 20:21
• I'm glad to help. Commented Feb 20, 2018 at 20:22