• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 476
  • Last Modified:

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

  • 2
1 Solution
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)
studioEtcAuthor Commented:
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.
try this

select cola, colb, max(colc)
from  (select case when cola <= colb then cola else colb end as cola,
    case when cola <= colb then colb else cola end as colb, colc
    from yourTable ) t1
group by cola,colb
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now