Link to home
Start Free TrialLog in
Avatar of Jim Singelis
Jim Singelis

asked on

Distinct But Reversed Pairs

I have a table similar to:

colA     colB  colC colD
3          11      ...     ...
54        22
11        3
22        11
22        54

I need only distinct pairs, regardless of the order of the elements of a pair.  That is 3, 11 is a duplicate of 11,3.  I need only one of the two rows. Can be either one.  So the above table should be:

colA     colB
3          11
54        22
22        11

Avatar of chapmandew
chapmandew
Flag of United States of America image

so, you only care about the values in columna and columnb?
select distinct cola,colb
from yourTable t1
where not exists (select 1 from yourtable t2 where t1.cola = t2.cola and t1.colb = t2.colb and cola>colb)
Avatar of Jim Singelis
Jim Singelis

ASKER

I didn't think I had any problem with other columns but unfortunately I do.  When there are duplicates, I need to retain the row with the highest value in colC.
ASKER CERTIFIED SOLUTION
Avatar of momi_sabag
momi_sabag
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial