Ufdjrw

Table: ID1 and ID2 are name of the column

| ID1 | ID2 | 
| 4 | 3 | 
| 3 | 2 | 
| 2 | 1 | 
| 7 | 6 | 
| 6 | 5 | 
| 9 | 8 | 

Desired Result

| ID1 | ID2 | 
| 4 | 1 | 
| 7 | 5 | 
| 9 | 8 | 

I need to build a recursive sql query for oracle using connect by or recursive cte. Unable to figure out solution.

No need to use CTE in this case since you do not do any cumulative calculations while traversing the tree.

SQL> with t(id1, id2) as
 2 (select 4,3 from dual
 3 union all select 3,2 from dual
 4 union all select 2,1 from dual
 5 union all select 7,6 from dual
 6 union all select 6,5 from dual
 7 union all select 9,8 from dual)
 8 select connect_by_root id1 id1, id2
 9 from t
 10 where connect_by_isleaf = 1
 11 start with not exists (select null from t t0 where t0.id2 = t.id1)
 12 connect by prior id2 = id1;

 ID1 ID2
---------- ----------
 4 1
 7 5
 9 8

This is just a supplement answer without using Hierarchical queries which removes common elements from id1 and id2.

 WITH t(id1, id2) 
 AS (SELECT 4, 
 3 
 FROM dual 
 UNION ALL 
 SELECT 3, 
 2 
 FROM dual 
 UNION ALL 
 SELECT 2, 
 1 
 FROM dual 
 UNION ALL 
 SELECT 7, 
 6 
 FROM dual 
 UNION ALL 
 SELECT 6, 
 5 
 FROM dual 
 UNION ALL 
 SELECT 9, 
 8 
 FROM dual), 
 t1 
 AS (SELECT id1 id1, 
 id1 id2 
 FROM t), 
 t2 
 AS (SELECT id2 id1, 
 id2 id2 
 FROM t), 
 t3 
 AS (SELECT ROWNUM row_num1, 
 id1 
 FROM (SELECT ( t.id1 ) id1 
 FROM t 
 WHERE NOT EXISTS (SELECT NULL 
 FROM t1, 
 t2 
 WHERE ( t1.id1 = t2.id1 
 AND t1.id2 = t2.id2 ) 
 AND ( t.id1 = t1.id1 )) 
 ORDER BY t.id1 ASC)), 
 t4 
 AS (SELECT ROWNUM row_num1, 
 id2 
 FROM (SELECT ( t.id2 ) id2 
 FROM t 
 WHERE NOT EXISTS (SELECT NULL 
 FROM t1, 
 t2 
 WHERE ( t1.id1 = t2.id1 
 AND t1.id2 = t2.id2 ) 
 AND ( t.id2 = t2.id2 )) 
 ORDER BY t.id2 ASC)) 
SELECT a.id1, 
 b.id2 
FROM t3 a, 
 t4 b 
WHERE a.row_num1 = b.row_num1 
ORDER BY id1;