CODEWITHSUNDEEP
t-sqljoinsql-server-2012self-join
42cornflakes
42cornflakes

Reputation: 303

Inner Joining a large query with itself

Problem:

I need to remove duplicate pairs from the result of a query (same problem as described here)

So if the result has (A,B), (B,A), (C,A)

I am only interested in (A,B) and (C,A)

The Complication: Unlike in the linked question, the data is not available in a table to perform an self join and retrieve easily. It is more in the following state

(SELECT C1, C2 from a mind boggling number of joins and unions)

So I can make it a temp table as follows

SELECT T.C1, T.C2
((SELECT C1, C2 from a mind boggling number of joins and unions)) T1

I would like to perform an inner join to remove duplicate pairs as mentioned above

So is there a way to do that in such a scenario Below query is syntactically wrong, but hopefully it conveys the idea

SELECT A.C1, A.C2
((SELECT C1, C2 from a mind boggling number of joins and unions)) T1 A
INNER JOIN T1 B
ON A.C1 = B.C1 AND
A.C2 < B.C2

I am running SQL Server 2012

Upvotes: 0

Views: 106

Answers (1)

radar
radar

Reputation: 13425

here is one way to achieve what you want with CTEs

you can as well use temporary table to store result and use cte1 alone.

with cte
as
( 
  select col1, col2 from --- your query here.

)
, cte1
as
(
  select col1, col2, row_number() over
       ( partition by (case when col1 >= col2 then col1
                            else col2
                            end) , 
                      (case when col1 <= col2 then col1
                            else col2
                      end) order by (select null)
       ) as rn
  from cte
  )
select * from cte1 where rn =1

Upvotes: 1

Related Questions