Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

ALTER SESSION SET NLS_DATE_FORMAT = 'fxYYYY-MM-DD HH24:MI:SS';
create table t_hierarchy (id, id_sup, id_type) AS
SELECT  1, null, 1 FROM DUAL UNION ALL
SELECT  2,  1, 1 FROM DUAL UNION ALL
SELECT  3,  2, 0 FROM DUAL UNION ALL
SELECT  4,  3, 0 FROM DUAL UNION ALL
SELECT  5,  4, 0 FROM DUAL UNION ALL
SELECT  6,  2, 1 FROM DUAL UNION ALL
SELECT  7,  6, 0 FROM DUAL UNION ALL
SELECT  8,  7, 0 FROM DUAL UNION ALL
SELECT  9,  2, 1 FROM DUAL UNION ALL
SELECT 10,  9, 0 FROM DUAL UNION ALL
SELECT 11, 10, 0 FROM DUAL UNION ALL
SELECT 12,  2, 1 FROM DUAL UNION ALL
SELECT 13, 12, 0 FROM DUAL UNION ALL
SELECT 14, 13, 0 FROM DUAL UNION ALL
SELECT 15, 14, 0 FROM DUAL UNION ALL
SELECT 16, 12, 0 FROM DUAL UNION ALL
SELECT 17, 16, 0 FROM DUAL UNION ALL
SELECT 18, 17, 0 FROM DUAL
18 rows affected

SELECT CONNECT_BY_ROOT id AS id,
       CONNECT_BY_ROOT id_sup AS id_sup,
       CONNECT_BY_ROOT id_type AS id_type,
       CASE id_type WHEN 0 THEN id END AS last_ancestor
FROM   t_hierarchy t
WHERE  CONNECT_BY_ISLEAF = 1
CONNECT BY
       PRIOR id_sup = id
AND    PRIOR id_type = 0
AND    id_type = 0


ID
ID_SUP
ID_TYPE
LAST_ANCESTOR


1
null
1
null

2
1
1
null

3
2
0
3

4
3
0
3

5
4
0
3

6
2
1
null

7
6
0
7

8
7
0
7

9
2
1
null

10
9
0
10

11
10
0
10

12
2
1
null

13
12
0
13

14
13
0
13

15
14
0
13

16
12
0
16

17
16
0
16

18
17
0
16

ID	ID_SUP	ID_TYPE	LAST_ANCESTOR
1	null	1	null
2	1	1	null
3	2	0	3
4	3	0	3
5	4	0	3
6	2	1	null
7	6	0	7
8	7	0	7
9	2	1	null
10	9	0	10
11	10	0	10
12	2	1	null
13	12	0	13
14	13	0	13
15	14	0	13
16	12	0	16
17	16	0	16
18	17	0	16