Suppress rows with reverse/swapped values

Question

I would like to query a database table that contains rows that have reverse values than other rows. So the table looks like this

Src            Trgt          ValueA          ValueB

A               B             1,44              5
B               A             1,44              5  <--
C               D             1,23              8
D               C             1,23              8  <--
F               G             5,12              9
G               F             5,12              9  <--

What I want is a query that returns all rows that do not again with the source and target value swapped. The rows that should not be queried are the ones that have the same Value A and B like another row, but only with source and target value swapped (The ones marked in above table) So, the desired results would look like this:

 Src            Trgt          ValueA          ValueB

A               B             1,44              5
C               D             1,23              8
F               G             5,12              9

What happened to D/E ? – Gordon Linoff Oct 09 '17 at 14:45 — Gordon Linoff, Oct 09 '17 at 14:45
My fault, updated the table. Thanks – Chris Oct 09 '17 at 14:57 — Chris, Oct 09 '17 at 14:57

Gordon Linoff · Answer 1 · 2017-10-14T13:31:46.330

1

I think this is what you want:

select t.*
from t
where t.src < t.trgt
union all
select t.*
from t
where t.src > t.trgt and
      not exists (select 1
                  from t t2
                  where t2.src = t.trgt and t2.trgt = t.src and
                        t2.a = t.a and t2.b = t.b
                 );

It keeps the first row encountered, filtering out equivalent rows where the first two columns are switched.

EDIT:

Another approach if you just one one row per combo is:

select least(src, trgt) as src, greatest(src, trgt) as trgt, a, b
from t
group by least(src, trgt), greatest(src, trgt), a, b;

This runs the risk of returning a row not in the original data (if the row has no duplicate and trgt > src.

edited Oct 14 '17 at 13:31

answered Oct 09 '17 at 14:46

Gordon Linoff

1,242,037
58
646
786

Is there no way to do this without a union? If the table t would be switched with an extensive sub query, I think things would be rather slow. – Chris Oct 09 '17 at 14:58
First select will ignore records with src = trgt (replace < by <= ???), inserted the missing "not" before "exists", Second select will not work because a and b are not enclosed in some aggregate function ! Could maybe be rewritten using window functions. – Christoph G Oct 11 '17 at 22:00
@ChristophG . . . The OP makes no statement on what to do when the src and target are equal. – Gordon Linoff Oct 14 '17 at 11:59
@GordonLinoff ... right, that depends on how you interpret the question. Still, there is a "not" missing before the "exists" in the first statement, the second statement should work after your edit. Always difficult to give a precise answer without precise spec and given table definition... – Christoph G Oct 14 '17 at 13:13
@ChristophG . . . That would be a valid criticism. I fixed it. – Gordon Linoff Oct 14 '17 at 13:32

wildplasser · Answer 2 · 2017-10-14T13:56:13.077

1

SELECT *
FROM ztable zt
WHERE zt.source < zt.target -- pick only one of the twins
OR NOT EXISTS(              -- OR :if it is NOT part of a twin
        SELECT *
        FROM ztable nx
        WHERE nx.source = zt.target
        AND nx.target = zt.source
        );

Assuming that rows with source=target are not present or not wanted.

edited Oct 14 '17 at 13:56

answered Oct 14 '17 at 12:23

wildplasser

43,142
8
66
109

Suppress rows with reverse/swapped values

2 Answers2