X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id ADCC29DCA5E
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 10 Mar 2006 10:31:47 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 74653-09
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 10 Mar 2006 10:31:51 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from peufeu.com (boutiquenumerique.com [82.67.9.10])
	by postgresql.org (Postfix) with ESMTP id 0709B9DCA33
	for <pgsql-sql@postgresql.org>; Fri, 10 Mar 2006 10:31:44 -0400 (AST)
Received: (qmail 30620 invoked from network); 10 Mar 2006 15:32:31 +0100
Received: from boutiquenumerique.com (HELO apollo13) (82.67.9.10)
	by boutiquenumerique.com with SMTP; 10 Mar 2006 15:32:31 +0100
Date: Fri, 10 Mar 2006 15:32:29 +0100
To: "Fay Du" <fay.du@versaterm.com>, pgsql-sql@postgresql.org
Subject: Re: How can I selet rows which have 2 columns values cross equal?
References: <001d01c64445$fec23350$b8d849c6@versaterm.com>
From: PFC <lists@peufeu.com>
Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Message-ID: <op.s57b0fvbcigqcu@apollo13>
In-Reply-To: <001d01c64445$fec23350$b8d849c6@versaterm.com>
User-Agent: Opera M2/8.51 (Linux, build 1462)
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.105 required=5 tests=[AWL=0.105]
X-Spam-Score: 0.105
X-Spam-Level: 
X-Archive-Number: 200603/149
X-Sequence-Number: 24495


What are your conditions on a and b ? Can a be equal to b on a row ? If  
so, do you want this row ?
If you want to avoid duplicates, I suggest first removing them, then  
adding a constraint CHECK( a<b ) for instance. Then, from you r  
application (or in an ON INSERT trigger), swap a and b if a>b.

I added some values to your table for completeness :

SELECT * FROM test;
   a  |  b  | id
-----+-----+----
  100 | 101 |  1
  101 | 100 |  2
  100 |   3 |  3
   20 |  30 |  4
   11 |  13 |  5
    3 |  33 |  6
   30 |  20 |  7
  666 | 666 |  8
  666 | 666 |  9
  500 | 666 | 10
  666 | 500 | 11
  123 | 123 | 12
  456 | 789 | 13
  456 | 789 | 14

Try :

SELECT x.id AS xid, y.id AS yid, x.a AS xa, x.b AS xb, y.a AS ya, y.b AS  
yb FROM test x, test y WHERE x.a=y.b AND x.b=y.a;

  xid | yid | xa  | xb  | ya  | yb
-----+-----+-----+-----+-----+-----
    1 |   2 | 100 | 101 | 101 | 100
    2 |   1 | 101 | 100 | 100 | 101
    4 |   7 |  20 |  30 |  30 |  20
    7 |   4 |  30 |  20 |  20 |  30
    8 |   9 | 666 | 666 | 666 | 666
    8 |   8 | 666 | 666 | 666 | 666
    9 |   9 | 666 | 666 | 666 | 666
    9 |   8 | 666 | 666 | 666 | 666
   10 |  11 | 500 | 666 | 666 | 500
   11 |  10 | 666 | 500 | 500 | 666
   12 |  12 | 123 | 123 | 123 | 123


You'll get 2 rows for each match. You can add a condition to remove the  
dupes :

SELECT x.id AS xid, y.id AS yid, x.a AS xa, x.b AS xb, y.a AS ya, y.b AS  
yb FROM test x, test y WHERE x.a=y.b AND x.b=y.a AND x.id<=y.id;

  xid | yid | xa  | xb  | ya  | yb
-----+-----+-----+-----+-----+-----
    1 |   2 | 100 | 101 | 101 | 100
    4 |   7 |  20 |  30 |  30 |  20
    8 |   9 | 666 | 666 | 666 | 666
    8 |   8 | 666 | 666 | 666 | 666
    9 |   9 | 666 | 666 | 666 | 666
   10 |  11 | 500 | 666 | 666 | 500
   12 |  12 | 123 | 123 | 123 | 123

If you don't want the rows with a=b, replace x.id<=y.id with x.id<y.id

SELECT x.id AS xid, y.id AS yid, x.a AS xa, x.b AS xb, y.a AS ya, y.b AS  
yb FROM test x, test y WHERE x.a=y.b AND x.b=y.a AND x.id<y.id;


  xid | yid | xa  | xb  | ya  | yb
-----+-----+-----+-----+-----+-----
    1 |   2 | 100 | 101 | 101 | 100
    4 |   7 |  20 |  30 |  30 |  20
    8 |   9 | 666 | 666 | 666 | 666
   10 |  11 | 500 | 666 | 666 | 500

It is going to be slow, though. Basically a full self join. Let's hack  
this :

CREATE AGGREGATE array_accum (
     sfunc = array_append,
     basetype = anyelement,
     stype = anyarray,
     initcond = '{}'
);

I'll leave this one as an exercice to the reader ;)))

SELECT array_accum(id), CASE WHEN a<b THEN a ELSE b END AS low, CASE WHEN  
a<b THEN b ELSE a END AS high FROM test GROUP BY low, high HAVING  
sum(sign(a-b)) = 0 AND count(*)>1;
  array_accum | low | high
-------------+-----+------
  {10,11}     | 500 |  666
  {4,7}       |  20 |   30
  {1,2}       | 100 |  101
  {8,9}       | 666 |  666



On Fri, 10 Mar 2006 14:24:44 +0100, Fay Du <fay.du@versaterm.com> wrote:

> Hi All:
> I have a table like this:
>
>    Table test
> Id   |  a | b
> -----+----+---
> 1    | 100| 101
> 2    | 101| 100
> 3    | 100| 3
> 4    | 20 | 30
> 5    | 11 | 13
> 6    | 3  | 33
> 7    | 30 | 20
>
> I want to get row 1, 2,4 and 7 selected. Because their values of a and b
> are cross equal. i.e., for each pair of rows,
> a.Row1 = b.Row2 and b.Ro1 = a.Row2
> a.Row4 = b.Row7 and b.Ro4 = a.Row7
>
> How can I construct a sql statement to select them?
> Thanks in advance.
> Fay
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster