CODEWITHSUNDEEP
sqlsql-servert-sql
nnmmss
nnmmss

Reputation: 2974

eliminating duplicate value in sql statement

I have a Sql Statement which its result is something like following

  TagNo1         TagNo2
 ---------------------------
   63516         63546
   63546         63516

How can set it that I need just one record . For example

    TagNo1         TagNo2
 ---------------------------
     63516         63546

or the other, no difference. because it is the same for me?

Thank you

Upvotes: 2

Views: 56

Answers (2)

paparazzo
paparazzo

Reputation: 45096

declare @t table (col1 int, col2 int);
insert into @t values (63516, 63546), (63546, 63516);

select col1, col2 
from @t 
where col1 <= col2 
union 
select col2, col1 
from @t 
where col2 < col1

Upvotes: 1

jarlh
jarlh

Reputation: 44696

Return a row if TagNo1 is less or equal to TagNo2. Or if the switched values don't exist.

select distinct TagNo1, TagNo2
from tablename t1
where TagNo1 <= TagNo2
   or not exists (select 1 from tablename t2
                  where t1.TagNo1 = t2.TagNo2
                    and t1.TagNo2 = t2.TagNo1)

Do select distinct to avoid duplicates if TagNo1 = TagNo2.

Or use case expressions:

select distinct case when TagNo1 <= TagNo2 then TagNo1 else TagNo2 end,
                case when TagNo1 >= TagNo2 then TagNo1 else TagNo2 end
from tablename

Upvotes: 4

Related Questions