Oracle 21c

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

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