SQLTeam.com | Weblogs | Forums

Select multiple row once


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

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 ?


Try this

select top(1) with ties
  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