Re: A unique pairs version of UNNEST() ?

> On 04 Jan 2016, at 21:08, Wells Oliver <wells(dot)oliver(at)gmail(dot)com> 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}

Your example reminds me of combinatory theory. The result you seem to be looking for is the list of possible unique combinations, as sets of elements of the total set (sets are orderless).

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;

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.