Eliminate rows with columns with inverted values

Question

I am having so much trouble trying to work this out. Can somebody help me?

I have a table with colA and colB and these as the values for each row:

colA  ColB
2     1
1     2

When selecting in this table, I want to retrieve just one row since the values are inverted. If 2 implies 1, 1 implies 2, so they are equal and I don't want two rows as a return.

Is there any way of doing a SQL query and get the result I want? For example, just one row with colA = 1, colB = 2.

Do you want to retrieve a row with more columns or just the two values? Is `(colA, colB)` unique? A table definition would clarify things. Version of Postgres, too. — Erwin Brandstetter, Nov 01 '14 at 01:50
If the permutations are considered duplicates, you might be better off with a [unique index](http://stackoverflow.com/a/18361539/939860) preventing such duplicates. [Related case for 3 columns.](http://stackoverflow.com/a/18361539/939860) — Erwin Brandstetter, Nov 01 '14 at 01:56

vyegorov · Accepted Answer · 2014-11-03T20:27:46.030

4

If it is only 2 columns:

WITH t(colA, colB) AS (
    VALUES (1,2), (2,1)
)
SELECT DISTINCT least(colA, colB), greatest(colA, colB)
  FROM t;

EDIT: As outlined by Daniel, although this might do the job, this approach is not good for filtering as it'll change the original data.

edited Nov 03 '14 at 20:27

answered Oct 31 '14 at 19:45

vyegorov

21,787
7
59
73

This produces a (colB,colA) row when colA>colB even if there is no such row in the source table. It might solve the asker's problem or not, but that's not a filtering technique. – Daniel Vérité Nov 03 '14 at 18:32
@DanielVérité, I don't understand this. Could you provide a sample, please? – vyegorov Nov 03 '14 at 19:03
1

Try with just one row as input: `VALUES (1,0)`. Result: `0,1`. – Daniel Vérité Nov 03 '14 at 19:11

score 3 · Answer 2 · answered Oct 31 '14 at 19:53

3

No indication was made as to which version of the tuple is preferred over the other. I.e., we know that (1,2) is equivalent to (2,1) and in that case one of the two should be display, but which of the two? In this solution, the assumption is that the variant with the lower ColA value is preferred over the reverse.

Select ColA, ColB
From SourceData As S
Where ColA < ColB
    Or Not Exists   (
                    Select 1
                    From SourceData As S1
                    Where S1.ColA = S.ColB
                        And S1.ColB = S.ColA
                    )

answered Oct 31 '14 at 19:53

Thomas

63,911
12
95
141

ColA <= ColB would be better otherwise the query will ignore rows where ColA = ColB – Multisync Oct 31 '14 at 20:00
@Multisync - There is no indication as to whether `ColA = ColB` is allowed or whether duplicates are allowed. – Thomas Nov 04 '14 at 16:49

score 3 · Answer 3 · answered Oct 31 '14 at 22:02

The SQL SELECT ... EXCEPT SELECT ... construct may be used for this. Also it will probably be faster than queries involving a self-join, especially without any index. I would suggest:

SELECT colA, colB FROM table
  EXCEPT
SELECT colB, colA FROM table WHERE colA < colB;

Duplicates rows are eliminated (if not needed or not desirable, use EXCEPT ALL).

Rows where colA=colB are not a special case, they go into the result, de-duplicated.

score 1 · Answer 4 · answered Oct 31 '14 at 19:41

1

A self join might work.

select t1.cola, t1.colb
from tablename t1 join tablename t2 on something
where t1.cola <> t2.colb
and t1.colb <> t2.cola

answered Oct 31 '14 at 19:41

Dan Bracuk

20,699
4
26
43

score 0 · Answer 5 · answered Oct 31 '14 at 19:43

0

How about using arrays?

select a, b from t;

/*
 a | b 
---+---
 1 | 2
 2 | 1
(2 rows)
*/

create extension intarray;
select distinct sort(ARRAY[a, b]) from t;

/*    
 sort  
-------
 {1,2}
(1 row)
*/

answered Oct 31 '14 at 19:43

Jared Beck

16,796
9
72
97

Eliminate rows with columns with inverted values

5 Answers5