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

|

1555 Discussions

|

  • sruthi_shst
     + 0 comments

    My solution:

    with cte1 as (select x, y, row_number() over( order by x) as ro_num from Functions), cte2 as (select x, y from cte1 oq where y in (select x from cte1 where ro_num != oq.ro_num and y = oq.x)) select top((select count(*) from cte2)/2) x, y from cte2 order by x;

  • 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