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}