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 23Solution – 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