Received: from localhost (maia-5.hub.org [200.46.204.182])
	by postgresql.org (Postfix) with ESMTP id 8ECF89FB605
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Mon, 19 Feb 2007 12:58:40 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024)
	with ESMTP id 24175-01 for <pgsql-sql-postgresql.org@postgresql.org>;
	Mon, 19 Feb 2007 12:58:27 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4
Received: from nz-out-0506.google.com (nz-out-0506.google.com
 [64.233.162.235])
	by postgresql.org (Postfix) with ESMTP id 53A529FB8C1
	for <pgsql-sql@postgresql.org>; Mon, 19 Feb 2007 12:58:27 -0400 (AST)
Received: by nz-out-0506.google.com with SMTP id i11so1964005nzh
	for <pgsql-sql@postgresql.org>; Mon, 19 Feb 2007 08:58:26 -0800 (PST)
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition;
	b=kFw3z2/AK5Eh8RbG+YK7O2BQMaTHNLPEAPdEYrO/mpdIZGIxtywJQDVj7O2GHX5CKIRNOsx4dE2gT+WDQ+rrW9x8DBcMU/4GkrBYdhzQvgGMHTD3mA9GikVkeZszT+mtGibIEJC0lE9W0COlxoBRCNIG6BuMJqdZsbYPjEOcLBY=
Received: by 10.64.204.19 with SMTP id b19mr9856441qbg.1171904306330;
	Mon, 19 Feb 2007 08:58:26 -0800 (PST)
Received: by 10.64.112.16 with HTTP; Mon, 19 Feb 2007 08:58:26 -0800 (PST)
Message-ID: <2049e51a0702190858n6932461dgc294d05b035aad53@mail.gmail.com>
Date: Mon, 19 Feb 2007 16:58:26 +0000
From: "Salman Tahir" <salmantahir1@gmail.com>
To: pgsql-sql@postgresql.org
Subject: cartesian product
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200702/200
X-Sequence-Number: 27534

Hi,

I have a query regarding an SQL statement I'm trying to execute. I
have the following table:

sequence
-------------+
AK
AKCMK
CMKA

I execute the following statement (Cartesian product):

SELECT p1.sequence as sequence1, p2.sequence as sequence2
FROM potential_pairs p1, potential_pairs p2

which gives me:

sequence1 | sequence2
----------------+--------------
 AK           | AK
  AK           | AKCMK
  AK           | CMKA
  AKCMK    | AK
  AKCMK    | AKCMK
  AKCMK    | CMKA
  CMKA      | AK
  CMKA      | AKCMK
  CMKA      | CMKA
(9 rows)

I want to eliminate duplicates and by duplicate I mean a tuple such as
{AK, CMKA} should be regarded as the same as {CMKA, AK}. So I would
like the following result:

sequence1 | sequence2
----------------+--------------
 AK           | AK
  AK           | AKCMK
  AK           | CMKA
  AKCMK    | AKCMK
  AKCMK    | CMKA
  CMKA      | CMKA

Any help would be appreciated.

- Salman