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
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
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)
from yourTable t1
where not exists (select 1 from yourtable t2 where t1.cola = t2.cola and t1.colb = t2.colb and cola>colb)
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.