From: | Andy Colson <andy(at)squeakycode(dot)net> |
---|---|
To: | Wells Oliver <wells(dot)oliver(at)gmail(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: A unique pairs version of UNNEST() ? |
Date: | 2016-01-04 20:14:03 |
Message-ID: | 568AD28B.8040708@squeakycode.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 1/4/2016 2:08 PM, Wells Oliver wrote:
> Hey all, happy new year.
>
> I am trying to get unique pairs from an array of N numbered items,
> usually 5, but possibly 4 or 6.
>
> If I just wanted unique values, I could do SELECT UNNEST(list_of_ids) AS
> id, COUNT(*) FROM table GROUP BY id but in this situation I want all
> unique pairs and a COUNT.
>
> For those familiar with python, this is the functionality found in
> itertools.combinations. I'm leaning towards just doing this in python,
> but I really like keeping as much in SQL as possible.
>
> So in an example where list_of_ids is {1,2,3,4,5} I would essentially get:
>
> {1, 2}
> {1, 3}
> {1, 4}
> {1, 5}
> {2, 3}
> {2, 4}
> {2, 5}
> {3, 4}
> {3, 5}
> {4, 5}
>
>
> Any tips? Thanks!
>
> --
> Wells Oliver
> wells(dot)oliver(at)gmail(dot)com <mailto:wellsoliver(at)gmail(dot)com>
if you could convert the array to a table then cross join it. Something
like:
select a.*, b.*
from unnest( {1,2,3,4,5} ) a
cross join unnest( {1,2,3,4,5} ) b
-Andy
From | Date | Subject | |
---|---|---|---|
Next Message | Edson Richter | 2016-01-04 20:18:38 | Re: BDR and TX obeyance |
Previous Message | Riley Berton | 2016-01-04 20:09:01 | BDR and TX obeyance |