How to delete duplicate results in SQL

Question

I have the following table with two columns which is generated by a query in SQL:

Lookup Value  Result
1             2
2             1
4             3
3             4

As you can see it contains duplicate results. I only want it to show the first line and the third line. Does anyone know how to do this in SQL?

Thanks

Possible duplicate of [How can I remove duplicate rows?](http://stackoverflow.com/questions/18932/how-can-i-remove-duplicate-rows) — Thunder_Lord79, Feb 09 '17 at 14:54
@OTARIKI In [**dominoes**](https://en.wikipedia.org/wiki/Dominoes) are the same. — Juan Carlos Oropeza, Feb 09 '17 at 15:02

score 2 · Accepted Answer · edited Feb 09 '17 at 15:38

2

There are several methods. Here is one using union all:

select t.*
from t
where col1 < col2
union all
select t1.*
from t1
where col1 > col2 and
      not exists (select 1 from t t2 where t1.col1 = t2.col2 and t1.col2 = t2.col1);

If you always know that both pairs exist (as in your sample data), you can just use:

select t.*
from t
where col1 < col2;

edited Feb 09 '17 at 15:38

t-clausen.dk

43,517
12
59
92

answered Feb 09 '17 at 14:53

Gordon Linoff

1,242,037
58
646
786

score 1 · Answer 2 · answered Feb 09 '17 at 14:59

1

SELECT DISTINCT 
       CASE WHEN Lookup Value  < Result
            THEN Lookup Value
            ELSE Result
       END as first,
       CASE WHEN Lookup Value  < Result
            THEN Result
            ELSE Lookup Value
       END as second
FROM YourTable

answered Feb 09 '17 at 14:59

Juan Carlos Oropeza

47,252
12
78
118

Juan .. this might generate rows which aren't in the table when there are no symmetric duplicates. Eg: (6,4) might come out as (4,6) – Vamsi Prabhala Feb 09 '17 at 15:02
1

@vkp, You are totally right. But Op says he want `(1,2)` over `(2,1)` but `(4,3)` over `(3,4)` for no particular reason. So my guess is tuple order doesnt matter, like dominoes pieces. – Juan Carlos Oropeza Feb 09 '17 at 15:04

score 0 · Answer 3 · answered Feb 09 '17 at 15:31

0

Create Table T (
[Lookup Value] int,
Result int
)

Insert into T values (1,2),(2,1),(4,3),(3,4)

Select distinct T.[Lookup Value], T.Result
From T
where T.[Lookup Value]<=T.Result

answered Feb 09 '17 at 15:31

Ric_R

145
2
11

How to delete duplicate results in SQL

3 Answers3