SQL select distinct combination of values on multiple columns

Question

I've checked everywhere for a simple solution to this and I can not find it. Say you have a database

column A	column B
function A	function B
function B	function A
function C	function D
function D	function C
function E	function F
function H	function G

I want to get rid of the combination of unique values, so I output this

column A	column B
function A	function B
function C	function D
function E	function F
function H	function G

Is there an elegant way to achieve this?

Is it possible to have unique combinations as well (ex: ColA = 'Fct 3' / ColB = 'Fct 17')? Are the combination always the same, I mean: could 'function 1' be associated to 'function 20' as well? — Christophe
– Christophe, Commented Sep 16, 2021 at 14:04
Just trying to understand the requirement here - is it the case that you want combination of values from the columns whereas in your source table you have permutation of values? — AhmedHuq
– AhmedHuq, Commented Sep 16, 2021 at 14:59
Yes, any permutation of values in columns that are not distinct should be discarded — Ben Nicholl
– Ben Nicholl, Commented Sep 16, 2021 at 15:28

Gordon Linoff · Accepted Answer · 2021-09-17 02:25:08Z

2

You can use <:

select t.*
from t
where column_a < column_b;

This will select one row from each reversed pair, the one where column_a has the lower value.

EDIT:

The question changed after I answered it. That is pretty rude, but I'll adjust the answer anyway:

select t.*
from t
where column_a < column_b or 
      not exists (select 1
                  from t t2
                  where t2.column_a = t.column_b and
                        t2.column_b = t.column_a
                 );

edited Sep 17, 2021 at 2:25

answered Sep 16, 2021 at 13:55

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ben Nicholl Over a year ago

What if instead of function 1 we have function A, function B, etc?

Ben Nicholl Over a year ago

Also updated my question to include other non combination rows

AhmedHuq Over a year ago

@gordon-linoff : will this solution work if the example table's 1st or 3rd row are not present.

Collectives™ on Stack Overflow

SQL select distinct combination of values on multiple columns

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related