We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.

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

# Symmetric Pairs

# Symmetric Pairs

#### Sort by

recency

#### |

#### 1336 Discussions

#### |

Please Login in order to post a comment

select least(x,y) as x1, greatest (x,y) as y1 '

from

(select *, row_number() over(partition by multi,divi order by divi) as row_num

from

(select x,y, (x*y )multi, (Greatest(x,y)/least(x,y)) divi

from functions) as data1) as data2 where row_num =2 order by x1 asc

oracle : select c.x, c.y from (select b.x, b.y , count(

) from (select a.x, a.y from functions a, functions b where a.x = b.y and b.x = a.y) b group by b.x, b.y having count() > 1 or b.x < b.y)c order by c.x ;select t2.X1 as a, t2.Y1 as b from (SELECT CAST(SUBSTRING_INDEX(t1.A, ' ', 1) AS SIGNED) AS X1, CAST(SUBSTRING_INDEX(t1.A, ' ', -1) AS SIGNED) AS Y1 FROM (SELECT DISTINCT CONCAT(X, ' ', Y) AS A FROM Functions f1 WHERE CONCAT(X, ' ', Y) IN (SELECT CONCAT(Y, ' ', X) FROM Functions f2 where Y!=X) ORDER BY A) AS t1 ORDER BY X1 ASC) as t2 where t2.X1<=t2.Y1 union select CAST(SUBSTRING_INDEX(m1.B, ' ', 1) AS SIGNED) AS a, CAST(SUBSTRING_INDEX(m1.B, ' ', -1) AS SIGNED) AS b from (SELECT CONCAT(X, ' ', Y) as B FROM Functions where X=Y group by CONCAT(X, ' ', Y) having count(CONCAT(X, ' ', Y))>1 ) as m1 order by a asc

with my_cte as ( select x, y, row_number() over (order by x) as "rank" from Functions ) select distinct a1.x, a1.y from my_cte a1 join my_cte a2 on a1.x = a2.y and a2.x = a1.y

where a1.rank != a2.rank and a1.x <= a1.y order by a1.x

Assign an id to each pair by using ROW_NUMBER, and then self join to look for simmetrical pairs, using the pair_id to avoid comparing the pair with itself. Lastly use distinct to remove duplicate pairs. WITH ordered_list as (SELECT ROW_NUMBER() OVER (ORDER BY x) pair_id, x, y FROM functions) SELECT DISTINCT f1.x, f1.y FROM ordered_list f1 JOIN ordered_list f2 ON f1.x=f2.y AND f2.x=f1.y AND f1.pair_id != f2.pair_id WHERE f1.x<=f1.y ORDER BY f1.x