Re: [GENERAL] A unique pairs version of UNNEST() ?

2016-01-05 Thread Kevin Grittner
On Tue, Jan 5, 2016 at 1:59 AM, Alban Hertroys wrote: > with list_of_ids as ( > select unnest(list_of_ids) as id from table > ) > select a.id, b.id > from list_of_ids a, list_of_ids b > where b.id > a.id; Or, to morph this to array output (which the OP seemed to want): test=# with list_of_

Re: [GENERAL] A unique pairs version of UNNEST() ?

2016-01-05 Thread Alban Hertroys
> On 04 Jan 2016, at 21:08, 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 B

Re: [GENERAL] A unique pairs version of UNNEST() ?

2016-01-04 Thread Andreas Kretschmer
Andy Colson wrote: >> 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.oli...@gmail.com

Re: [GENERAL] A unique pairs version of UNNEST() ?

2016-01-04 Thread Andy Colson
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

[GENERAL] A unique pairs version of UNNEST() ?

2016-01-04 Thread Wells Oliver
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