Jeff Moden (1/14/2010)
shaini.binu (1/14/2010)
[font="Arial Black"]Quote: Am I right that you'd need to start a new group as soon as neither one of the numbers in one row has been previously assigned to a group?Ans: Yes, you are correct ,[/font]
Ans :
If the linktable contains data :
SID(primary key)|ID1 |ID2
====================
1| 101 | 102
2| 102 | 101
3| 103 | 104
4| 103 | 105
5| 104 | 105
6| 101 | 104
7| 107 | 108
8| 101 | 109
The expected output is:
GroupNum| ID
==========
1| 101
1| 102
1| 103
1| 104
1| 105
2| 107
2| 108
1| 109
Thanks & Regards
Sh.
Then why doesn't 103, 104, and 105 live in a group 2 by themselves?
As far as I would read it, row #6 makes 103 to 105 belonging to group 1.
Instead of "previously assigned" the term probably should be "assigned anywhere in the table".
I think to solve this puzzle you'd need to order the rows asc by the value of either id1 or id2 (per row), depending on which one is less than the other...
Using the example from above you'd end up with
SID(primary key)|ID1 |ID2
====================
1| 101 | 102
2| 101 | 102 (ID1 and ID2 switched)
6| 101 | 104
8| 101 | 109
3| 103 | 104
4| 103 | 105
5| 104 | 105
7| 107 | 108
... at least that's what I guess should be done to get the expected result...