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;
Hello another way to solve this in Oracle but without using connect by clause is this:
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
), cte(id1, id2) AS (
SELECT id1, id2
FROM t
WHERE NOT EXISTS (SELECT 1 FROM t t0 WHERE t0.id2 = t.id1)
UNION ALL
SELECT cte.id1, t.id2
FROM t
JOIN cte ON t.id1 = cte.id2
)
SELECT id1, min(id2)as id2
FROM cte
group by id1
order by 1;
Hope it helps.
