Thread: How can I selet rows which have 2 columns values cross equal?

How can I selet rows which have 2 columns values cross equal?

From
"Fay Du"
Date:
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



Re: How can I selet rows which have 2 columns values cross equal?

From
Milorad Poluga
Date:
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
---------------------------------------



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 |
7666 | 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




Re: How can I selet rows which have 2 columns values cross equal?

From
Bruno Wolff III
Date:
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
;


Locking row

From
Flavio Suguimoto
Date:
Hi all,

I need to mark a row with a value in a column, but first i need to select
the first row without this mark. But in some concurrents cases i mark the
row twice. How can i lock the row to avoid others session get it?

TABLE TICKET
TICKET_NUMBER | MARK       00001 |    1       00002 |    0

I need to select the first row with 0 in MARK column and then mark it with
1.

regards,
Flavio Suguimoto



Re: Locking row

From
"Larry Rosenman"
Date:
Flavio Suguimoto wrote:
> Hi all,
> 
> I need to mark a row with a value in a column, but first i need to
> select the first row without this mark. But in some concurrents cases
> i mark the row twice. How can i lock the row to avoid others session
> get it? 
> 
> TABLE TICKET
> TICKET_NUMBER | MARK
>         00001 |    1
>         00002 |    0
> 
> I need to select the first row with 0 in MARK column and then mark it
> with 1.


look at the FOR UPDATE in the select docs.



-- 
Larry Rosenman                     http://www.lerctr.org/~ler
Phone: +1 512-248-2683                 E-Mail: ler@lerctr.org
US Mail: 430 Valona Loop, Round Rock, TX 78681-3893



Re: Locking row

From
Michael Fuhr
Date:
On Fri, Mar 10, 2006 at 02:57:51PM -0600, Larry Rosenman wrote:
> Flavio Suguimoto wrote:
> > I need to mark a row with a value in a column, but first i need to
> > select the first row without this mark. But in some concurrents cases
> > i mark the row twice. How can i lock the row to avoid others session
> > get it? 
> > 
> > TABLE TICKET
> > TICKET_NUMBER | MARK
> >         00001 |    1
> >         00002 |    0
> > 
> > I need to select the first row with 0 in MARK column and then mark it
> > with 1.
> 
> look at the FOR UPDATE in the select docs.

If you use FOR UPDATE with LIMIT then see the following in the
SELECT documentation:
   It is possible for a SELECT command using both LIMIT and FOR   UPDATE/SHARE  clauses to return fewer rows than
specifiedby   LIMIT.  This is because LIMIT is applied first.  The command   selects the specified number of rows, but
mightthen block   trying to obtain lock on one or more of them.  Once the SELECT   unblocks, the row might have been
deletedor updated so that   it does not meet the query WHERE condition anymore, in which   case it will not be
returned.

For example, suppose you have the following data:
ticket_number | mark 
---------------+------00001         |    100002         |    000003         |    0
(3 rows)

Two concurrent transactions, T1 and T2, both run the following
query:

SELECT * FROM ticket WHERE mark = 0 LIMIT 1 FOR UPDATE;

One transaction, say T1, will get the following row:
ticket_number | mark 
---------------+------00002         |    0
(1 row)

T2 will block until T1 ends.  If T1 rolls back or doesn't update
the row then T2 will get the above row.  But if T1 updates the row
so that mark = 1 and then commits, then T2 will get an empty result
set instead of getting the next row with mark = 0.  The queries
could use LIMIT 2 instead of LIMIT 1 and update only the first row
that came back, but then you'd have the same problem with a third
concurrent transaction (and with LIMIT 3 and a fourth transaction,
and so on).

-- 
Michael Fuhr


Re: How can I selet rows which have 2 columns values cross equal?

From
Michael Glaesemann
Date:
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



Re: How can I selet rows which have 2 columns values cross equal?

From
Michael Glaesemann
Date:
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