SQLTeam.com | Weblogs | Forums

Select multiple row once


#1

Hello

I have a table created as result from complex condition matched as:
ID ID_1 ID_2

ID: is identity col
ID_1: has 1:N relation with ID_2
ID_2: has 1:N relation with ID_1

sample:
ID ID_1 ID_2
.. 700 201
.. 700 300
.. 201 700
.. 300 700

I need to select column (ID_1, ID_2) select once:

700, 201 and 201,700 are the same row also,
700, 300 and 300, 700 are the same row

and must be select once, How to write select ?

Regards,
sasan.


#2

Try this

Query
select top(1) with ties
       id_1
      ,id_2
  from yourtable
 order by row_number() over(partition by case when id_1<=id_2 then id_1 else id_2 end
                                        ,case when id_1>id_2 then id_1 else id_2 end
                                order by id
                           )
;