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.
  1. Prepare
  2. SQL
  3. Advanced Join
  4. Symmetric Pairs
  5. Discussions

Symmetric Pairs

Sort by

recency

|

1524 Discussions

|

  • mudit4hire
     + 0 comments
    WITH CTE AS (
SELECT t.X as x1,t.Y as y1,COUNT(*) as cnt FROM (
SELECT X,Y FROM Functions WHERE X<=Y
UNION ALL
SELECT Y,X FROM Functions WHERE Y!=X
) AS t
GROUP BY t.X,t.Y
HAVING COUNT(*) > 1
)

SELECT x1,y1 from CTE
ORDER BY x1
``

  • kulaisaikumar091
     + 0 comments
    with cte as(
select *,
case 
    when t.x=t.y and  (SELECT COUNT(*) FROM functions AS temp WHERE temp.x = t.y AND temp.y = t.x) > 1 then 'true'
    when t.x<t.y and (select count(*) from functions as temp where temp.x=t.y and temp.y=t.x)>0 then 'true'
    else ''
    end as flag
from functions as t
group by x,y 

)
select x,y
from cte
where flag='true'
order by x

  • arunima221199
     + 0 comments

    the question is incorrect for case where we have the following input: 20 21 21 20 20 21 21 20 in this case 20 21 should be printed twice applying distinct while selecting the rows will only print 20 21 once

  • l7arconada
     + 0 comments
    WITH rns AS (
    SELECT   x, y, ROW_NUMBER() OVER(ORDER BY x) as rn
    FROM     functions f
)
SELECT   DISTINCT f1.x, f1.y
FROM     rns f1
         LEFT JOIN rns f2 ON f1.x = f2.y AND f1.y = f2.x AND f1.rn <> f2.rn
WHERE    f2.x IS NOT NULL AND f1.x <= f1.y
ORDER BY f1.x

  • me22btech11026
     + 0 comments

    SELECT f1.x, f1.y FROM functions f1 JOIN functions f2 ON f1.x = f2.y AND f2.x = f1.y WHERE f1.x < f1.y

    UNION

    SELECT x, y FROM functions GROUP BY x, y HAVING x = y AND COUNT(*) > 1

    ORDER BY x, y;