Get the distinct of two columns that are the reverse of each other in SSMS

Question

Apologies if this is a duplicate question, I can't seem to find it anywhere else.

I have a table like so:

column1 column2 column3
entry 1 A   B
ENTRY 2 A   C
ENTRY 3 B   C
ENTRY 1 B   A
ENTRY 2 C   A
ENTRY 3 C   B

The table I'm using has more columns but the idea is the same.

Is there an easy clean way to get the distinct entries (i.e. I'm only interested in bringing back Entry 1 once, not twice.

score 5 · Answer 1 · answered Jul 25 '18 at 12:11

5

If columns 2 and 3 contain "reverse duplicates" that you want to hide, you will have to decide what ordering you want to see:

SELECT column1, column2, column3
FROM aTable
WHERE column2 <= column3

answered Jul 25 '18 at 12:11

Wolfgang Kais

4,010
2
10
17

Cetin Basoz · Accepted Answer · 2018-07-25T12:27:56.010

Select distinct t1.Column1, 
  case when t1.Column2 < t2.Column2 then t1.Column2 else t2.Column2 end as Column2,   
  case when t1.Column3 > t2.Column3 then t1.Column3 else t2.Column3 end as Column3  
from myTable t1
inner join myTable t2 on t1.Column1 = t2.Column1 
     and t1.column2 = t2.column3;

EDIT: A simpler one:

Select t1.* 
  from myTable t1
  inner join myTable t2 on t1.Column1 = t2.Column1 
       and t1.Column2 = t2.Column3
  where t1.column2 < t1.column3

EDIT2: And if you want to also return rows where there is no such dupes:

Select t1.* 
from myTable t1
left join myTable t2 on t1.Column1 = t2.Column1 
   and t1.Column2 = t2.Column3
where t1.column2 < t1.column3 or t2.COlumn1 is null;

Gordon Linoff · Answer 3 · 2018-07-25T12:21:18.540

1

You can use exists to find the duplicates and then < (or >) to get one of the rows;

select t.*
from t
where exists (select 1
              from t t2
              where t2.column1 = t1.column1 and
                    t2.column2 = t1.column3 and
                    t2.column3 = t1.column2
              ) and
      t.column1 < t.column2;

edited Jul 25 '18 at 12:21

answered Jul 25 '18 at 12:08

Gordon Linoff

1,242,037
58
646
786

Did you mean `not exists` and `or`? – Wolfgang Kais Jul 25 '18 at 12:14
@WolfgangK . . . No. The OP wants duplicates, but only wants one row with them. – Gordon Linoff Jul 25 '18 at 12:14
... ok, I didn't see (and still don't) that requirement. So you meant "You can use `exists`". :-) – Wolfgang Kais Jul 25 '18 at 12:20

score 1 · Answer 4 · answered Jul 25 '18 at 12:23

From your data I assumed, that each entry has the same column1 value, if they are duplicate. Try:

SELECT column1, column2, column3 FROM (
    SELECT column1,
           column2,
           column3,
           ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY column2, column3) rn
    FROM MyTable
) a WHERE rn = 1

score 0 · Answer 5 · answered Jul 25 '18 at 12:18

I your case you can use distinct with outer apply. in outer apply you can add order by that you need

select distinct
  t.column1, 
  r.column2,
  r.column3
from myTable t
outer apply (
  select top 1
    r.column2,
    r.column3
  from myTable as r
  where r.column1 = t.column1
) as r

score 0 · Answer 6 · answered Oct 17 '19 at 08:31

All the answer till now, will loose data for single combination entry. It is suppose to be below code

Select distinct t1.Column1, 
  case when t1.Column2 < t1.Column3 then t1.Column2 else t1.Column3 end as Column2,   
  case when t1.Column2 < t1.Column3 then t1.Column3 else t1.Column2 end as Column3  
from myTable t1

Get the distinct of two columns that are the reverse of each other in SSMS

6 Answers6