Search Postgresql Archives

Complex Recursive Query

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux