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
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.