How to get final column of parent id in oracle connect via sql

Question

1 Create a table like this

  CREATE TABLE oracle_connet_by_test (
     item_id NUMBER PRIMARY KEY,
     parent_id NUMBER,
     item_desc VARCHAR2 (8));

     INSERT INTO oracle_connet_by_test VALUES (1, 0, 'AAA');
     INSERT INTO oracle_connet_by_test VALUES (2, 0, 'BBB');
     INSERT INTO oracle_connet_by_test VALUES (3, 1, 'CCC');
     INSERT INTO oracle_connet_by_test VALUES (4, 2, 'DDD');
     INSERT INTO oracle_connet_by_test VALUES (5, 1, 'EEE');
     INSERT INTO oracle_connet_by_test VALUES (6, 3, 'FFF');
     INSERT INTO oracle_connet_by_test VALUES (7, 3, 'GGG');
     commit;

2 When I run this script, the output will be

  SQL> SELECT LEVEL,
       2 ITEM_ID,
       3 PARENT_ID, LPAD ('', 4 * (LEVEL - 1)) ||  ITEM_DESC AS ITEM_DESC
       4 FROM ORACLE_CONNET_BY_TEST
       5 START WITH parent_Id = 0
       6 CONNECT BY PRIOR ITEM_ID = PARENT_ID
       7;

          LEVEL ITEM_ID PARENT_ID ITEM_DESC
     ---------- ---------- ---------- -----------
              1 1 0 AAA
              2 3 1 CCC
              3 6 3 FFF
              3 7 3 GGG
              2 5 1 EEE
              1 2 0 BBB
              2 4 2 DDD

3 QUESTION: If I need this result, how do I write code?

  LEVEL ITEM_ID PARENT_ID ITEM_DESC SUPER_ID
     ---------- ---------- ---------- ---------------- ---- ------
              1 1 0 AAA 1
              2 3 1 CCC 1
              3 6 3 FFF 1
              3 7 3 GGG 1
              2 5 1 EEE 1
              1 2 0 BBB 2
              2 4 2 DDD 2

Answer 1

use connect_by_root:

SELECT LEVEL, ITEM_ID, PARENT_ID, LPAD(' ', 4 * (LEVEL - 1)) || ITEM_DESC AS ITEM_DESC , connect_by_root item_id AS SUPER_ID FROM ORACLE_CONNET_BY_TEST START WITH parent_Id = 0 CONNECT BY PRIOR ITEM_ID = PARENT_ID ;