Re: A unique pairs version of UNNEST() ?

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.oliver@gmail.com <mailto:wellsoliver@gmail.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