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

  • aslamshaw97
     + 0 comments

    Assign row numbers using ROW_NUMBER() to prevent self-joins and ensure each row is uniquely identifiable.

    The join condition finds symmetric pairs (X1 = Y2, X2 = Y1), and f1.rn < f2.rn avoids joining a row with itself and also make sures that X1 < X2 and Y1 > Y2.

    WITH RankedRows AS (SELECT ROW_NUMBER() OVER (ORDER BY X, Y) AS rn, X, Y FROM Functions),
Pairs AS (SELECT f1.X AS X1, f1.Y AS Y1, f2.X AS X2, f2.Y AS Y2  
          FROM RankedRows f1 JOIN RankedRows f2 
          ON f1.X = f2.Y AND f2.X = f1.Y AND f1.rn < f2.rn)
SELECT X1, Y1 FROM Pairs ORDER BY X1;