- Prepare
- SQL
- Advanced Join
- Symmetric Pairs
- Discussions

# Symmetric Pairs

# Symmetric Pairs

+ 29 comments My MS SQL solution:

SELECT f1.X, f1.Y FROM Functions f1 INNER JOIN Functions f2 ON f1.X=f2.Y AND f1.Y=f2.X GROUP BY f1.X, f1.Y HAVING COUNT(f1.X)>1 or f1.X<f1.Y ORDER BY f1.X

+ 14 comments The question should be made a little more clear. When selecting the symmetric pair, it assumes x<=y . This is not mentioned in the problem. (for the pair x1,y1 and y1,x1 select only the one which has x<=y) Also for y=f(x), if x=y, it can be assumed that f(y)=x, however we need to find two pairs of x,y to consider that. This works as a great SQL problem, but the scenario is incorrect. We cannot think of this as a mathematical function.

+ 6 comments Here's a simple solution that avoids using joins or subqueries:

SELECT A.x, A.y FROM FUNCTIONS A JOIN FUNCTIONS B ON A.x = B.y AND A.y = B.x GROUP BY A.x, A.y HAVING COUNT(A.x) > 1 OR A.x < A.y ORDER BY A.x

The key is the HAVING line, with two conditions.

The first condition in that line makes sure pairs with the same X and Y values don't get to count themselves as their symmetric pair. (e.g. if 10 10 appears one time it's not counted as a symmetric pair, but as 13 13 appears twice, that is a symmetric pair)

The second condition ensures that only one of a pair is displayed. (e.g. if 3 24 and 24 3 form a symmetric pair, it will only display 3 24, not 24 3 as well.)

+ 13 comments My solution:

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;

+ 5 comments My simple solution using SQL Server:

;WITH CTE_Functions AS ( SELECT X, Y, ROW_NUMBER() OVER (PARTITION BY (X + Y), ABS(X-Y) ORDER BY X DESC) AS RowNumber FROM Functions WITH (NOLOCK) ) SELECT DISTINCT X, Y FROM CTE_Functions WHERE RowNumber > 1 AND X <= Y

This solution handles all possibilities including duplicates and negative values. No need to use JOINs or Sub queries or any complex logic. It is very straightforward, concise and simple solution.

Sort 737 Discussions, By:

Please Login in order to post a comment