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

|

1435 Discussions

|

  • hetvipatel9398
     + 0 comments

    with f1_temp as ( select x, y, count(*) as cnt from functions where x <= y group by x , y )

    , f2_temp as( select x, y from functions where x > y )

    select f1.x, f1.y from f1_temp f1 where f1.cnt >1 or ( f1.x in (select f2.y from f2_temp f2 ) and f1.y in (select f2.x from f2_temp f2 ) ) order by f1.x asc

  • srinjit35
     + 0 comments

    Without using CTEs:-

    SELECT X, Y FROM ( (SELECT FN1.X AS X, FN1.Y AS Y
    FROM FUNCTIONS FN1 JOIN FUNCTIONS FN2 ON FN1.X = FN2.Y AND FN1.Y = FN2.X
    AND FN1.X <> FN1.Y AND FN1.X <= FN1.Y ) UNION (SELECT FN3.X AS X, FN3.Y AS Y FROM FUNCTIONS FN3 WHERE FN3.X = FN3.Y GROUP BY FN3.X, FN3.Y HAVING COUNT(*) > 1 ) ) ALIAS ORDER BY X

  • ngoccth
     + 0 comments

    ngoccth_SQL SERVER: WITH table_1 AS ( SELECT F1.X As X, F1.Y as Y, COUNT(F1.X) as num FROM Functions AS F1 INNER JOIN Functions AS F2 ON F1.X = F2.Y AND F1.Y = F2.X AND F1.X <= F1.Y AND F2.X >= F2.Y GROUP BY F1.X, F1.Y ) SELECT X, Y from table_1 WHERE X != Y OR (X = Y AND num > 1) ORDER BY X

  • shushantgambhir1
     + 0 comments

    WITH CTE1 AS( SELECT X,Y,ROW_NUMBER() OVER(ORDER BY X ) As row_n FROM Functions )

    SELECT DISTINCT X,Y FROM( SELECT a.X,a.Y FROM CTE1 AS a INNER JOIN CTE1 AS b On a.X=b.Y AND a.Y=b.X AND a.row_n!=b.row_n) t1 WHERE X<=Y

  • theflippedbit
     + 1 comment
    with functions_ranked as (
select x, y, row_number() over(order by x) as rn_idx
from functions),


joined_pairs as (
select t1.x as x1, t1.y as y1, t2.x as x2, t2.y as y2
from functions_ranked t1
join functions_ranked t2
on t1.rn_idx != t2.rn_idx
and t1.x = t2.y
and t1.y = t2.x)

select distinct x1, y1
from joined_pairs
where x1<=y1
order by x1