- Practice
- SQL
- Advanced Join
- Symmetric Pairs
- Discussions

# Symmetric Pairs

# Symmetric Pairs

anish749 + 5 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.

luizhguimaraes + 0 comments great explanation!

- TP
tperiyasamy + 0 comments Yes, when f(x)=y, then it can be X-20 = Y also. X need not be always less than Y. Y is just a calculation based on X.

- SK
shabeenakhoja + 3 comments Why should x<=y? Can you please explain?

anish749 + 0 comments A year back when I was solving this, the correct solution assumed that x<=y. It was not mentioned in the question.

- YA
yashrajarora0 + 0 comments @alexey_filippov ne kaha jaise.. ek function f(x) = y hai toh woh x ki ek value pe y ki ek hi value aa sakti hai, lekin yahan yeh contradictory case hai ki x ki ek value 20 pe humein y ki do values i.e., 20 and 21 mil rahi hai.. ;)

dave_shubham_2 + 0 comments Basically, when you don't consider x<=y, you count one pair as two different elements The answer requires you to find pairs. For example, For case x < y: 2 24 and 24 2 are two sets of X and Y values which form a symmetric pair. Then, you have to list only 2 24 in your answer and not both of these as per the question.

For case x = y: You have to have atleast two unique sets of X and Y values present in the "Functions" table to list one of these sets in your answer.

If you only one set of X and Y with X = Y, although mathematically it'd form a symmetric relation but there is no other set here with which this current set is forming a pair.

I hope this made it more clear.

- RS
RockyStark + 1 comment Select distinct a.x as w,a.y from functions a ,functions b Where a.x =b.y and b.x = a.y and a.x < b.x Union all Select a1.x as w,a1.y from functions a1 where a1.x = a1.y group by a1.x,a1.y having count(*)>1 Order by w;

Nothing wrong with this

rohanshenoy96 + 1 comment Can u explain Why you have included Second Select query?

dave_shubham_2 + 0 comments For case x = y: You have to have atleast two unique sets of X and Y values present in the "Functions" table to list one of these sets in your answer.

If you only one set of X and Y with X = Y, although mathematically it'd form a symmetric relation but there is no other set here with which this current set is forming a pair.

Second SELECT query is basically finding all values where X = Y and checking that if there is at least one other set which has same value and if there is, then only we consider it as a pair, otherwise discard it for the purpose of this question.

I hope this made it more clear.

- RK
rajnishdwivedy + 2 comments Thanks a lot, I was stuck for more than an hour with symmetric pairs but was not able to filter out one set.This explanation does make it simpler.

with f1 as ( SELECT x, y,row_number()over(partition by x,y order by x) as cnt FROM Functions) SELECT DISTINCT f1.x,f1.y from f1 inner join Functions f2 on f1.x=f2.y and f1.y=f2.x and (f1.x!=f1.y or cnt >1) where f1.x<=f1.y order by f1.x;

- MM
mihirscool + 1 comment Can you explain the second half, why is it required?

- RK
rajnishdwivedy + 0 comments Second half is basically finding all the semmetric pair by not including the ones where x=y more than once. Also " f1.x<=f1.y " this condition is not specified in the problem statement but it's required otherwise it is almost difficult to get desired answer.

RECRUZ02 + 0 comments Thank you for your solution, as I used it as reference to learn. This was harder than at first blush.

Here's my solution with a slight modification on the CTE. I choose not to "PARTITION OVER X,Y", as that makes it more confusing to me.

What I've done is assign a ROW_NUMBER as a UNIQUEIDENTIFIER for the rows. This allows me to join against "all rows that aren't myself" (F1.ID != F2.ID).

Tomayto, tomahto. Cheers! :)

WITH FUNCTIONS_CTE (ID, X, Y) AS ( SELECT ROW_NUMBER() OVER(ORDER BY X), X, Y FROM FUNCTIONS) SELECT DISTINCT F1.X, F1.Y FROM FUNCTIONS_CTE F1 INNER JOIN FUNCTIONS_CTE F2 ON F1.X = F2.Y AND F2.X = F1.Y AND F1.ID != F2.ID WHERE F1.X <= F1.Y ORDER BY F1.X

gin3002 + 10 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;

- SP
Salon72 + 0 comments great job mate.but a humble request,dont put ur ans here.we dont come(discussion tab) here for answers.

- SG
satvikgadam + 0 comments can you explain your query ?

- S
sanlex + 0 comments Explanation please?

nikhilvyasnikhil + 1 comment can u explain why f2.x>f1.x is required

- SR
sandipvinodrayr1 + 0 comments It is required to eliminate the repeatition of rows. For example there are two rows symmetrical 21, 12 and 12,21 then without f2.x >f1.x it will print both 21,12 and 12,21. But we want to print only one of them so writing either of f2.x>f1.x or f2.x

- PM
PaviMoger + 0 comments This code works for oracle too!!!!!!

- SZ
sg070839 + 1 comment Great work! but I think you can drop the "and (x!=y)" part because with (f1.y = f2.x > f1.x), x!=y is a must

- SL
shinanli_nn + 1 comment what is x! ? thanks

SSSNOWBALLS + 0 comments "!=" means not equal, so the way you read it, is x != y instead of x! = y

- JK
tingyuk + 3 comments Thanks for the solution. I'm wondering why it doesn't work if I change "where exists" to "where (x,y) in";

the second one the subquery doesn't seem to return anything...

- SZ
szavgorodni + 0 comments [deleted] - SZ
szavgorodni + 0 comments [deleted] - SZ
szavgorodni + 1 comment /* I think this is a bit easier to understand for somebody who prefers joins over exists */ select t.x , t.y from ( select a.x , a.y -- , b.x as xi -- , b.y as yi from functions a inner join functions b on a.x = b.y and a.y = b.x and a.x < a.y union select x , y -- , count(*) from functions a where 1=1 and x=y group by x,y having count(*) > 1 ) t order by t.x ,t.y ;

metinsenturk + 0 comments Thanks to @szavgorodni, I realized we should also consider the count of equals. Commenting rocks!

select a.x, a.y from ( -- tuples with not equals that follow the rule select f1.x, f1.y from functions f1 join functions f2 on (f1.x = f2.y and f2.x = f1.y and (f1.x != f1.y)) and f1.x < f1.y union -- tuples with the equals that follow the rule select f1.x, f1.y from functions f1 join functions f2 on (f1.x = f2.y and f2.x = f1.y and (f1.x = f1.y)) group by f1.x,f1.y having count(1) > 1 ) a order by a.x asc

- CR
chiranjitray_in1 + 0 comments Can you explain the second subquery.How is x=f1.x selecting the records that have been repeated more than one time

- TN
trungn2015 + 0 comments Less complex for the second SELECT:

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 GROUP BY x, y HAVING COUNT(*) > 1 order by x;

- MH
moeinhasani77 + 1 comment why cant we say x<=y and we use union instead?

metinsenturk + 0 comments Because the query says * wo pairs (X1, Y1) and (X2, Y2) are said to be symmetric pairs if X1 = Y2 and X2 = Y1. This statement does not define an 'y bigger than or equal to x ' relationship. In my solution the way I mapped this sentence was

f1.x = f2.y and f2.x = f1.y and (f1.x != f1.y) where f1, f2 are functions tables

Hope this helps.

- VK
vvk78 + 2 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

- SZ
szavgorodni + 0 comments great ! compact and clean !

- AB
czakron + 0 comments Nice!

- SM
san28v + 0 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.

alexey_filippov + 2 comments The

*Y is the value of some function F at X -- i.e. Y = F(X)*clause seems to contradict the example given, e.g. for`X = 20`

there are two values of`Y`

,`20`

and`21`

- CH
colshil + 1 comment You're perfectly right. The statement of the problem and the test cases are not consistent.

- N
nirav_pat2005 + 2 comments select distinct x,y from functions a where exists (select 1 from functions b where a.x = b.y and b.x = a.y and a.x <= a.y and a.rowid <> b.rowid ) order by x,y;

- SC
ram9151 + 1 comment can u please explain

aayushicumar + 0 comments its not working. but what is it that confused you...?

- SG
soniagoyal2192 + 0 comments it is not working in my sql

ulitjd + 0 comments You're right!

In Mathematics:

- Function is a mapping from a member in the domain to a member in its range. (one member only, cannot be none or mor than one)

This is a "relation" NOT a function!

- Relation is a mapping from one member of a set to some(none or many) members of another set.

pranaysameer + 3 comments MY SOLUTION select distinct f1.x,f1.y from functions f1 join functions f2 on(f1.y=f2.x) where f2.y=f1.x order by f1.x;

Can anyone tell why its not working?

The_Economist + 2 comments it doesnt work because if i have one distinct row 20,20 then this gets captured while it shouldn't.

pranaysameer + 0 comments Thanks @The_Economist

anish749 + 0 comments [deleted]

nikhilvyasnikhil + 0 comments Please explain why is this not working - I wrote similar query. what should be changed here ?

- CJ
zhangzz2015 + 1 comment SELECT f1.x,f1.y FROM functions AS f1 JOIN functions AS f2 ON f1.x=f2.y AND f1.y=f2.x WHERE f1.x

this is my code, but it doesn't work...

ps42wallabywayS + 0 comments I also made this one at the first time. But the problem is, if there is a record x=y (example: (1,1),(2,2)) even if it's only one row, this code will return these records which are not symmetric pairs.

- M
michaelngelo + 1 comment select distinct f1.x, f1.y from functions f1 join functions f2 where f1.y=f2.x and f1.x=f2.y and f1.x<f1.y union select x, y from functions where x=y group by x,y having count(*)>1 order by x;

sanjay_dev + 1 comment purpose of using count please detail explanation

some people have written like after union

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;

- CF
ChristinaFong01 + 0 comments need to have 2 pairs to be called as symmetric

- JS
johnwstump + 1 comment SELECT x, y FROM functions AS f1 /* This is not made clear as part of the problem statement, but we only want the 'first' instance of any symmetric pair, as ranked by x ascending. Setting the condition that y >= x ensures that the first instance is the one we include. */ WHERE y >= x GROUP BY 1, 2 /* Here's the meat of the query. We count the number of symmetric pairs we can find for the current x, y pair. There are three possible values: there might be no symmetric pairs, in which case we exclude the current pair; there might be 1 symmetric pair; or there might be two. The tricky bit here is that if a pair consists of (x,y) such that x = y, then we can always find one pair - itself - which looks to be symmetric. So when x = y, that's when we want to find two symmetric pairs - itself and its 'actual' symmetric partner. This ends up being easier than it sounds. */ HAVING COUNT(( SELECT x FROM functions f2 /* Set the WHERE conditions from the definition of symmetric pair */ WHERE f2.x = f1.y AND f2.y = f1.x LIMIT 1) /* As explained above, if x = y, we want to find two results, otherwise we want one. */ ) = IF(x = y, 2, 1) /* Finally, as indicated in the problem, we order by ascending x */ ORDER BY 1 ASC;

woojoestar + 0 comments Thank you for the comments.

goodson_jonathon + 0 comments This is not a very well-defined problem. It's prescribed output is in conflict with its sample output, so I have no idea what to return.

- YZ
yz3045 + 1 comment This is my code based on MySQL

select f1.x, f1.y from functions as f1 inner join functions as f2 on f2.y = f1.x where f1.y >= f1.x and f2.x = f1.y group by f1.x, f1.y having count(*) > 1 or (count(*) = 1 and f1.y != f1.x) order by f1.x

harshitha2922 + 1 comment can u explain me the last 2 lines??

- YZ
yz3045 + 0 comments If x!=y, the count should be 1 because I set a condition that y>=x; if x=y, the count should be larger than 1.

Sort 260 Discussions, By:

Please Login in order to post a comment