anish749 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 great explanation!
tperiyasamy 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.
shabeenakhoja Why should x<=y? Can you please explain?
anish749 A year back when I was solving this, the correct solution assumed that x<=y. It was not mentioned in the question.
yashrajarora0 @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 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.
pyshgl Lets say there are two symmetric rows in table; x=2, y=24 and x=24,y=2. Then x<=y eliminates the possibility of counting the pairs twice for same set.
RockyStark 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 Can u explain Why you have included Second Select query?
dave_shubham_2 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.
rajnishdwivedy 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;
mihirscool Can you explain the second half, why is it required?
rajnishdwivedy 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 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
rs_anantmishra the example clarifies it...
gin3002 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;Salon72 great job mate.but a humble request,dont put ur ans here.we dont come(discussion tab) here for answers.
jayushi_94 obviously everyone visit this TAB for explanation and answers. Y do we have this tab? ---> For disscusing our queries and it's explanation. LOOK AT YOUR DOWNVOTES.
eswistak Kinda ruins the fun if you are just copying and pasting answers all day, no?
satvikgadam can you explain your query ?
sanlex Explanation please?
nikhilvyasnikhil can u explain why f2.x>f1.x is required
sandipvinodrayr1 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
PaviMoger This code works for oracle too!!!!!!
sg070839 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
shinanli_nn what is x! ? thanks
SSSNOWBALLS "!=" means not equal, so the way you read it, is x != y instead of x! = y
tingyuk 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...
szavgorodni [deleted]szavgorodni [deleted]szavgorodni /* 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 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
CalmerThanYouAre Nicely put together. Note that
and (f1.x != f1.y)is redundant due to the requirement
f1.x < f1.yenesturedigw61 Great solution, also another thing that comes to mind is, us not having to use join for the second part. Basically:
f1.x = f2.y and f2.x = f1.y and (f1.x = f1.y) means (f1.x = f1.y) and instead of join we could use where in order to reduce the process load:
select f1.x, f1.y from functions f1 where f1.x = f1.y group by f1.x,f1.y having count(1) > 1
chiranjitray_in1 Can you explain the second subquery.How is x=f1.x selecting the records that have been repeated more than one time
trungn2015 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;
moeinhasani77 why cant we say x<=y and we use union instead?
metinsenturk 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.
anton_russinov A little bit shorter solution:
select distinct s.x, s.y from Functions s, Functions s1 where s.x = s1.y and s.y = s1.x and s.x <= s1.x and s.rowid <> s1.rowid order by s.x;
vvk78 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
szavgorodni great ! compact and clean !
czakron Nice!
xzhang66 Could anyone explain why the code has 'or f1.x < f1.y' ?? Thanks!
julianvalencia88 The criteria in the having clause allows us to prevent duplication in our output while still achieving our goal of finding mirrored pairs. We have to treat our pairs where f1.x = f1.y and f1.x <> f1.y differently to capture both. The first criteria handles pairs where f1.x = f1.y and the 2nd criteria handles pairs where f1.x <> f1.y, which is why the or operator is used.
The first part captures records where f1.x = f1.y. The 'count(f1.x) > 1' requires there to be at least two records of a mirrored pair to be pulled through. Without this a pair would simply match with itself (since it's already it's own mirrored pair) and be pulled through incorrectly when you join the table on itself.
The 2nd part matches the remaining mirrored pairs. It's important to note that for this challenge, the mirrored match of (f1.x,f1.y) is considered a duplicate and excluded from the final output. You can see this in the sample output where (20, 21) is outputted, but not (21,20). The 'or f1.x < f1.y' criteria allows us to pull all those pairs where f1.x does not equal f1.y, but where f1.x is also less than f1.y so we don't end up with the mirrored paired duplicate.
san28v 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.
hallelmal Beautiful and elegant solution.
schmidtabox Can you please explain RowNumber > 1 ?
I am trying to figure out how RowNumber became a count, without using COUNT()
alexey_filippov 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 = 20there are two values ofY,20and21colshil You're perfectly right. The statement of the problem and the test cases are not consistent.
nirav_pat2005 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;
ram9151 can u please explain
aayushicumar its not working. but what is it that confused you...?
soniagoyal2192 it is not working in my sql
ulitjd 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 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 it doesnt work because if i have one distinct row 20,20 then this gets captured while it shouldn't.
pranaysameer Thanks @The_Economist
anish749 [deleted]
nikhilvyasnikhil Please explain why is this not working - I wrote similar query. what should be changed here ?
zhangzz2015 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 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.
michaelngelo 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 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;
ChristinaFong01 need to have 2 pairs to be called as symmetric
johnwstump 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 Thank you for the comments.
ghazi_bousselmi Guessing again, in so many challenges. I think if more difficulty is to be added to a problem, that should not be done in the clearness of its definition, but rather straight forward in the task itself.
yz3045 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 can u explain me the last 2 lines??
yz3045 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 317 Discussions, By:
Please Login in order to post a comment