Query for ignore duplicated rows

Question

question

SSGC asked Mar 14, '14

Query for ignore duplicated rows

I have table like #D: CREATE TABLE #D(ID1 INT, ID2 INT) insert into #D SELECT 1,2 UNION SELECT 2,1 UNION SELECT 3,4 UNION SELECT 4,3 UNION SELECT 5,7 UNION SELECT 7,5 SELECT * FROM #D DROP TABLE #D but we think (2,1),(4,3),(7,5) are duplicated rows. How we can have a query only choose (1,2),(3,4),(5,7) for the results? Alterative, you can get something like (1,2), (4,3),(7,5) also fine. Anyone can help me for this case? for real case I have thousands row like this. Thanks.

t-sql join row

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2014-03-14T03:44:06Z

Squirrel answered Mar 14, '14

SELECT ID1, ID2 FROM ( SELECT *, RN = row_number() over (partition by CASE WHEN ID1 ID1 THEN ID2 ELSE ID1 END order by ID1 ) FROM #D ) D WHERE RN = 1

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

SSGC commented · Mar 14, 2014 at 11:47 AM

Thank you very much! it is work very well. I use a while loop can do it. I think your query more efficient.

0 ·

question