Recursive sql query in oracle

Question

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.

Answer 1

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