0

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. – McNets Feb 20 '18 at 19:57
2

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 – Kevin Patterson Feb 20 '18 at 20:21
  • I'm glad to help. – McNets Feb 20 '18 at 20:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.