pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow can I selet rows which have 2 columns values cross equal?
6+ messages / 5 participants
[nested] [flat]
* How can I selet rows which have 2 columns values cross equal?
@ 2006-03-10 13:24 Fay Du <fay.du@versaterm.com>
2006-03-10 14:27 ` Re: How can I selet rows which have 2 columns values cross equal? Milorad Poluga <milorad.poluga@cores.co.yu>
2006-03-10 14:32 ` Re: How can I selet rows which have 2 columns values cross equal? PFC <lists@peufeu.com>
2006-03-10 17:31 ` Re: How can I selet rows which have 2 columns values cross equal? Bruno Wolff III <bruno@wolff.to>
2006-03-11 07:46 ` Re: How can I selet rows which have 2 columns values cross equal? Michael Glaesemann <grzm@myrealbox.com>
0 siblings, 4 replies; 6+ messages in thread
From: Fay Du @ 2006-03-10 13:24 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How can I selet rows which have 2 columns values cross equal?
2006-03-10 13:24 How can I selet rows which have 2 columns values cross equal? Fay Du <fay.du@versaterm.com>
@ 2006-03-10 14:27 ` Milorad Poluga <milorad.poluga@cores.co.yu>
3 siblings, 0 replies; 6+ messages in thread
From: Milorad Poluga @ 2006-03-10 14:27 UTC (permalink / raw)
To: Fay Du <fay.du@versaterm.com>; +Cc: pgsql-sql
On Friday 10 March 2006 14:24, Fay Du wrote:
> 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
>
select a.*
from test a, test b
where a.a = b.b and a.b = b.a
order by id
Regards, Milorad Poluga
--
---------------------------------------
Milorad Poluga
HK CORES Beograd, Makenzijeva 31
milorad.poluga@cores.co.yu
---------------------------------------
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How can I selet rows which have 2 columns values cross equal?
2006-03-10 13:24 How can I selet rows which have 2 columns values cross equal? Fay Du <fay.du@versaterm.com>
@ 2006-03-10 14:32 ` PFC <lists@peufeu.com>
3 siblings, 0 replies; 6+ messages in thread
From: PFC @ 2006-03-10 14:32 UTC (permalink / raw)
To: Fay Du <fay.du@versaterm.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How can I selet rows which have 2 columns values cross equal?
2006-03-10 13:24 How can I selet rows which have 2 columns values cross equal? Fay Du <fay.du@versaterm.com>
@ 2006-03-10 17:31 ` Bruno Wolff III <bruno@wolff.to>
3 siblings, 0 replies; 6+ messages in thread
From: Bruno Wolff III @ 2006-03-10 17:31 UTC (permalink / raw)
To: Fay Du <fay.du@versaterm.com>; +Cc: pgsql-sql
On Fri, Mar 10, 2006 at 08:24:44 -0500,
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.
Self join the table.
Something like (untested):
SELECT t1.id, t1.a, t1.b
FROM test t1, test t2
WHERE
t1.a = t2.b
AND
t1.b = t2.a
ORDER BY t1.id
;
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How can I selet rows which have 2 columns values cross equal?
2006-03-10 13:24 How can I selet rows which have 2 columns values cross equal? Fay Du <fay.du@versaterm.com>
@ 2006-03-11 07:46 ` Michael Glaesemann <grzm@myrealbox.com>
2006-03-11 09:35 ` Re: How can I selet rows which have 2 columns values cross equal? Michael Glaesemann <grzm@myrealbox.com>
3 siblings, 1 reply; 6+ messages in thread
From: Michael Glaesemann @ 2006-03-11 07:46 UTC (permalink / raw)
To: Fay Du <fay.du@versaterm.com>; +Cc: pgsql-sql
On Mar 10, 2006, at 22:24 , Fay Du wrote:
> 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
You need to use subqueries:
create table test
(
id integer primary key
, a integer not null
, b integer not null
);
copy test (id, a, b) from stdin;
1 100 101
2 101 100
3 100 3
4 20 30
5 11 13
6 3 33
7 30 20
\.
select t1.id as t1_id, t2.id as t2_id
from test t1
join test t2 on (t1.a = t2.b and t1.b = t2.a);
t1_id | t2_id
-------+-------
7 | 4
4 | 7
2 | 1
1 | 2
(4 rows)
And if you don't want to have each pair listed twice, just add WHERE
t1.a < t2.a, e.g.,
select t1.id as t1_id, t2.id as t2_id
from test t1
join test t2 on (t1.a = t2.b and t1.b = t2.a)
where t1.a < t2.a;
t1_id | t2_id
-------+-------
4 | 7
1 | 2
(2 rows)
Hope this helps!
Michael Glaesemann
grzm myrealbox com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How can I selet rows which have 2 columns values cross equal?
2006-03-10 13:24 How can I selet rows which have 2 columns values cross equal? Fay Du <fay.du@versaterm.com>
2006-03-11 07:46 ` Re: How can I selet rows which have 2 columns values cross equal? Michael Glaesemann <grzm@myrealbox.com>
@ 2006-03-11 09:35 ` Michael Glaesemann <grzm@myrealbox.com>
0 siblings, 0 replies; 6+ messages in thread
From: Michael Glaesemann @ 2006-03-11 09:35 UTC (permalink / raw)
To: Michael Glaesemann <grzm@myrealbox.com>; +Cc: Fay Du <fay.du@versaterm.com>; pgsql-sql
On Mar 11, 2006, at 16:46 , Michael Glaesemann wrote:
> select t1.id as t1_id, t2.id as t2_id
> from test t1
> join test t2 on (t1.a = t2.b and t1.b = t2.a)
> where t1.a < t2.a;
> t1_id | t2_id
> -------+-------
> 4 | 7
> 1 | 2
> (2 rows)
Just a follow-up (mostly to myself): I've been toying with using
natural joins recently, and here's the same query rewritten to use a
natural join:
select id as t1_id, t2_id
from test t1
natural join (
select id as t2_id
, a as b
, b as a
from test
) t2
where id < t2_id;
t1_id | t2_id
-------+-------
4 | 7
1 | 2
(2 rows)
Michael Glaesemann
grzm myrealbox com
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2006-03-11 09:35 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-03-10 13:24 How can I selet rows which have 2 columns values cross equal? Fay Du <fay.du@versaterm.com>
2006-03-10 14:27 ` Milorad Poluga <milorad.poluga@cores.co.yu>
2006-03-10 14:32 ` PFC <lists@peufeu.com>
2006-03-10 17:31 ` Bruno Wolff III <bruno@wolff.to>
2006-03-11 07:46 ` Michael Glaesemann <grzm@myrealbox.com>
2006-03-11 09:35 ` Michael Glaesemann <grzm@myrealbox.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox