Re: Identifying pairs as groups

charlie3101_at_hotmail.com (Charlie Edwards) wrote in message news:<217ac5a8.0403220606.30defee4_at_posting.google.com>...
> I have the following data that identifies pairs. So 101 and 102 is a
> pair. But also 101 and 103 make a pair which logically means that 101,
> 102 and 103 make a group. What I need to do is identify each group
> along with some surrogate key
>
> ID1 ID2
> --- ---
> 101 102
> 101 103
> 102 101
> 102 103
> 103 101
> 103 102
> 201 202
> 202 201
> 301 302
> 301 303
> 301 304
> 302 301
> 302 303
> 302 304
> 303 301
> 303 302
> 303 304
> 304 301
> 304 302
> 304 303
>
> but what I want is ...
>
> ID GROUP
> --- -----
> 101 1
> 102 1
> 103 1
> 201 2
> 202 2
> 301 3
> 302 3
> 303 3
> 304 3
>
> Can anyone help?
>
> TIA
>
> CE

Charlie, in you example the group is equal to the first digit/character of the ID. If this or a similar pattern is true then look in the SQL Manual at the substr function. If would be very easy to assign a group based on the substr of the ID. If the logic is more complex look at the CASE statement.

HTH -- Mark D Powell -- Received on Mon Mar 22 2004 - 13:19:54 CST