Hello coders, today we are going to solve Symmetric Pairs HackerRank Solution in SQL.

Problem
You are given a table, Functions, containing two columns: X and Y.
Column | Type |
---|---|
X | Integer |
Y | Integer |
Two pairs (X1, Y1) and (X2, Y2) are said to be symmetric pairs if X1 = Y2 and X2 = Y1.
Write a query to output all such symmetric pairs in ascending order by the value of X. List the rows such that X1 ≤ Y1.
Sample Input
X | Y |
---|---|
20 | 20 |
20 | 20 |
20 | 21 |
23 | 22 |
22 | 23 |
21 | 20 |
Sample Output
20 20
20 21
22 23
Solution – Symmetric Pairs in SQL
MySQL
SELECT X, Y FROM FUNCTIONS F1 WHERE EXISTS (SELECT * FROM FUNCTIONS F2 WHERE F2.Y = F1.X AND F2.X = F1.Y AND F2.X > F1.X) AND (X != Y) UNION SELECT X, Y FROM FUNCTIONS F1 WHERE X = Y AND ( (SELECT COUNT(*) FROM FUNCTIONS WHERE X = F1.X AND Y = F1.X) > 1) ORDER BY X;
Disclaimer: The above Problem (Symmetric Pairs) is generated by Hacker Rank but the Solution is Provided by CodingBroz. This tutorial is only for Educational and Learning Purpose.
IN MS SQL
with cte as(SELECT X,Y, ROW_NUMBER() OVER(Partition by X ORDER BY X) rn
FROM Functions
WHERE X=Y
)
,cte1 as (SELECT X,Y
FROM cte
WHERE rn !=1 )
SELECT f1.X,f1.Y
FROM Functions f1
JOIN Functions f2
ON f1.X=f2.Y and f1.Y=f2.X
AND f1.X < f1.Y
UNION
SELECT X,Y
FROM cte1
ORDER BY X