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@xxxxxxxxx <mailto:wellsoliver@xxxxxxxxx>
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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
