We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
  1. Prepare
  2. SQL
  3. Advanced Join
  4. Symmetric Pairs
  5. Discussions

Symmetric Pairs

Sort by

recency

|

1554 Discussions

|

  • thfin7479
     + 0 comments
    from Functions f 
inner join Functions f1 on f.X = f1.Y and f.Y = f1.X
where f.x <= f.y
group by f.x,f.Y
having f.x < f.y or (f.x=f.y and count(*)>1)
order by f.x, f.y

  • bachuvikas2
     + 0 comments

    WITH CTE AS ( SELECT x,y,(x+y) AS sm, abs(x-y) AS sb FROM functions ), CTE2 AS( SELECT *, row_number() OVER(PARTITION BY sm, sb ORDER BY x) AS rnk FROM CTE) SELECT y,x FROM cte2 WHERE rnk = 2 ORDER BY y;

  • sumit2003k
     + 0 comments

    select distinct a.x, a.y from Functions a inner join functions b on a.x=b.y and b.x=a.y where a.x<=a.y

    -- to remove self join cases if equal pair (A,A) exist in the table, it will self join so i'll check the result in the base table that (A,A) cases should have atleast 1 entry

    and (a.x != a.y or ( select count(*) from functions where x=a.x and y=a.y )>1) order by a.x

  • dobrocry
     + 0 comments

    MS SQL Server

    WITH TBL AS(
    SELECT * FROM Functions WHERE x = y
    GROUP BY x, y HAVING COUNT(X) = 2
    UNION
    SELECT TOP 50 PERCENT * FROM Functions F
    WHERE x <> y 
    AND x IN (SELECT y FROM Functions WHERE x <> F.x)
    AND y IN (SELECT x FROM Functions WHERE y <> F.y)
)
SELECT * FROM TBL ORDER BY x

  • eddy_rene_c
     + 0 comments
    select distinct a.x,a.y
from functions a
inner join functions b
on a.x = b.y and a.y = b.x
where a.x <= a.y  AND (a.x != a.y OR (SELECT COUNT(*) 
                                        FROM functions 
                                        WHERE x = a.x AND y = a.y) > 1)
order by a.x