I have a collection of relationship rows of the form
Table: graph key1 varchar key2 varchar
A row of the form ('a','b') indicates that 'a' and 'b' are related. The table contains many relationships between keys, forming several disjoint sets. All relationships are bi-directional, and both directions are present. I.e. the table contains a set of disjoint graphs specified as node pairs.
For example the set of values
key1 key2 ----- ----- a x a y b w c t x a y a y z z y t c w b w d d w
defines three disjoint groups of connected keys:
a x y z c t b w d
What I would like to achieve is a single SQL query that returns
group key ----- --- 1 a 1 x 1 y 1 z 2 c 2 t 3 b 3 w 3 d
I don't care about preserving the node-to-node relationships, only the group membership for each node.
I've been playing with "WITH RECURSIVE" CTEs but haven't had any success. I'm not really sure how to express what I want in SQL, and it's not completely clear to me that recursive CTEs will help here. Also I'm not sure how to generate the sequence numbers for the groups
