From: | John W Higgins <wishdev(at)gmail(dot)com> |
---|---|
To: | Jim Garrison <jim(dot)garrison(at)nwea(dot)org> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Complex Recursive Query |
Date: | 2014-07-24 03:07:36 |
Message-ID: | CAPhAwGy74AJiXMBeaPYjQVzsFc7aykZr9CxJmq3e4bwE1E5-Zg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
https://gist.github.com/wishdev/635f7a839877d79a6781
Sorry for the 3rd party site - just easier to get the layout correct.....
A CTE and dense_rank is all it takes. I am always amazed at what one can
now pack into such small amounts of code.
On Wed, Jul 23, 2014 at 4:00 PM, Jim Garrison <jim(dot)garrison(at)nwea(dot)org> wrote:
> 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
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
From | Date | Subject | |
---|---|---|---|
Next Message | Fabio Milano | 2014-07-24 03:53:04 | Standby Server Bus 7 error |
Previous Message | matt | 2014-07-24 01:35:35 | Table checksum proposal |