By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE t (id, name, lvl ) as
select 1, 'element1', 1 from dual union all
select 2, 'element2', 2 from dual union all
select 3, 'element3', 3 from dual union all
select 4, 'element4', 3 from dual union all
select 5, 'element5', 3 from dual union all
select 6, 'element6', 3 from dual union all
select 7, 'element7', 2 from dual union all
select 8, 'element8', 3 from dual union all
select 9, 'element9', 4 from dual union all
select 10, 'element10', 4 from dual union all
select 11, 'element11', 1 from dual union all
select 12, 'element12', 2 from dual union all
select 13, 'element13', 3 from dual union all
select 14, 'element14', 4 from dual union all
select 15, 'element15', 4 from dual union all
select 16, 'element16', 3 from dual union all
select 17, 'element17', 4 from dual union all
select 18, 'element18', 4 from dual union all
select 19, 'element19', 1 from dual;
19 rows affected
select *
from t
MATCH_RECOGNIZE (
ORDER BY id DESC
MEASURES
child.id AS id,
child.name AS name,
child.lvl AS lvl,
parent.id AS parent_id
ONE ROW PER MATCH
AFTER MATCH SKIP TO NEXT ROW
PATTERN (child ancestors*? (parent | $))
DEFINE
parent AS lvl = child.lvl - 1
)
ORDER BY id
| ID | NAME | LVL | PARENT_ID |
|---|---|---|---|
| 1 | element1 | 1 | null |
| 2 | element2 | 2 | 1 |
| 3 | element3 | 3 | 2 |
| 4 | element4 | 3 | 2 |
| 5 | element5 | 3 | 2 |
| 6 | element6 | 3 | 2 |
| 7 | element7 | 2 | 1 |
| 8 | element8 | 3 | 7 |
| 9 | element9 | 4 | 8 |
| 10 | element10 | 4 | 8 |
| 11 | element11 | 1 | null |
| 12 | element12 | 2 | 11 |
| 13 | element13 | 3 | 12 |
| 14 | element14 | 4 | 13 |
| 15 | element15 | 4 | 13 |
| 16 | element16 | 3 | 12 |
| 17 | element17 | 4 | 16 |
| 18 | element18 | 4 | 16 |
| 19 | element19 | 1 | null |
select *
from t c
LEFT OUTER JOIN LATERAL(
SELECT p.id AS parent_id
FROM t p
WHERE c.id > p.id
AND c.lvl = p.lvl + 1
ORDER BY id DESC
FETCH FIRST ROW ONLY
)
ON (1 = 1)
ORDER BY id
| ID | NAME | LVL | PARENT_ID |
|---|---|---|---|
| 1 | element1 | 1 | null |
| 2 | element2 | 2 | 1 |
| 3 | element3 | 3 | 2 |
| 4 | element4 | 3 | 2 |
| 5 | element5 | 3 | 2 |
| 6 | element6 | 3 | 2 |
| 7 | element7 | 2 | 1 |
| 8 | element8 | 3 | 7 |
| 9 | element9 | 4 | 8 |
| 10 | element10 | 4 | 8 |
| 11 | element11 | 1 | null |
| 12 | element12 | 2 | 11 |
| 13 | element13 | 3 | 12 |
| 14 | element14 | 4 | 13 |
| 15 | element15 | 4 | 13 |
| 16 | element16 | 3 | 12 |
| 17 | element17 | 4 | 16 |
| 18 | element18 | 4 | 16 |
| 19 | element19 | 1 | null |
SELECT id, name, lvl, parent_id
FROM (
SELECT c.*,
p.id AS parent_id,
ROW_NUMBER() OVER (PARTITION BY c.id ORDER BY p.id DESC) AS rn
FROM t c
LEFT OUTER JOIN t p
ON (c.id > p.id AND c.lvl = p.lvl + 1)
)
WHERE rn = 1
ORDER BY id
| ID | NAME | LVL | PARENT_ID |
|---|---|---|---|
| 1 | element1 | 1 | null |
| 2 | element2 | 2 | 1 |
| 3 | element3 | 3 | 2 |
| 4 | element4 | 3 | 2 |
| 5 | element5 | 3 | 2 |
| 6 | element6 | 3 | 2 |
| 7 | element7 | 2 | 1 |
| 8 | element8 | 3 | 7 |
| 9 | element9 | 4 | 8 |
| 10 | element10 | 4 | 8 |
| 11 | element11 | 1 | null |
| 12 | element12 | 2 | 11 |
| 13 | element13 | 3 | 12 |
| 14 | element14 | 4 | 13 |
| 15 | element15 | 4 | 13 |
| 16 | element16 | 3 | 12 |
| 17 | element17 | 4 | 16 |
| 18 | element18 | 4 | 16 |
| 19 | element19 | 1 | null |