Removing duplicates from multiple self left joins

From: James Moriarty <jamespaulmoriarty(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Removing duplicates from multiple self left joins
Date: 2010-11-22 07:20:27
Message-ID: AANLkTimyMLaBO6RMjSEBW-gdovdh+g13J=QfuoBz_LtT@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I am dynamically generating a query like below that creates different
combinations of rules by left joining (any number of times) on itself and
avoiding rules with some of the same attributes as part of the joins
conditions e.g.

SELECT count(*)
FROM rules AS t1
LEFT JOIN rules AS t2
ON t1.id != t2.id
AND ...
LEFT JOIN rules AS t3
ON t1.id != t2.id AND t1.id != t3.id AND t2.id != t3.id
AND ...

I am currently removing duplicates by creating an array of ids from the
joined rows then sorting and grouping by them:

SELECT sort(array[t1.id, t2.id, t3.id])
...
GROUP BY ids

I would like to know if there is a better way of removing duplicate rows
e.g.

t1.ID | t2.ID | t3.ID
---------------------
A | B | C
C | B | A

Should be

t1.ID | t2.ID | t3.ID
---------------------
A | B | C

Or

t1.ID | t2.ID | t3.ID
---------------------
C | B | A

But not both.

I would like to go from a permutation of rows to a combination rows.

Browse pgsql-general by date

  From Date Subject
Next Message Adarsh Sharma 2010-11-22 07:32:06 What is Dialect
Previous Message Craig Ringer 2010-11-22 06:29:25 Re: Fwd: [pgsql-www] Forums at postgresql.com.au