This table (and its data) is used in the examples bellow:
CREATE TABLE table_connect_by ( id1 NUMBER(2,0) , parent_id1 NUMBER(2,0) , code VARCHAR(50) , CONSTRAINT pk_table_connect_by PRIMARY KEY (id1) , CONSTRAINT fk_table_connect_by FOREIGN KEY (parent_id1) REFERENCES table_connect_by (id1) );
INSERT INTO table_connect_by (id1, parent_id1, code) VALUES (21, null, 'B_21'); INSERT INTO table_connect_by (id1, parent_id1, code) VALUES (22, 21, 'B_21_22'); INSERT INTO table_connect_by (id1, parent_id1, code) VALUES (23, 21, 'B_21_23'); INSERT INTO table_connect_by (id1, parent_id1, code) VALUES (11, null, 'A_11'); INSERT INTO table_connect_by (id1, parent_id1, code) VALUES (12, 11, 'A_11_12'); INSERT INTO table_connect_by (id1, parent_id1, code) VALUES (13, 11, 'A_11_13'); INSERT INTO table_connect_by (id1, parent_id1, code) VALUES (14, 12, 'A_11_12_14'); INSERT INTO table_connect_by (id1, parent_id1, code) VALUES (15, 12, 'A_11_12_15'); INSERT INTO table_connect_by (id1, parent_id1, code) VALUES (16, 14, 'A_11_12_14_16'); INSERT INTO table_connect_by (id1, parent_id1, code) VALUES (17, 14, 'A_11_12_14_17'); COMMIT;

SELECT CONNECT_BY_ROOT -- précise la colonne qui sera le parent t1.id1 AS root_parent_id -- le parent principal , t1.id1 AS child_id -- Les enfants du parent principal , NVL(t1.parent_id1, 0) AS parent_id1 -- Le parent direct de l'enfant sélectionné , t1.code AS code , LEVEL AS level_parent_child -- La distance entre le parent principal et l'enfant sélectionné FROM table_connect_by t1 CONNECT BY PRIOR t1.id1 = t1.parent_id1;
Appliquer un "ORDER BY" sur le résultat de la requête :
SELECT * FROM ( SELECT CONNECT_BY_ROOT -- précise la colonne qui sera le parent t1.id1 AS root_parent_id -- le parent principal , t1.id1 AS child_id -- Les enfants du parent principal , NVL(t1.parent_id1, 0) AS parent_id1 -- Le parent direct de l'enfant sélectionné , t1.code AS code , LEVEL AS level_parent_child -- La distance entre le parent principal et l'enfant sélectionné FROM table_connect_by t1 CONNECT BY PRIOR t1.id1 = t1.parent_id1 ) ORDER BY root_parent_id, parent_id1, child_id;
Résultat de la requête : 11 11 0 A_11 1 11 12 11 A_11_12 2 11 13 11 A_11_13 2 11 14 12 A_11_12_14 3 11 15 12 A_11_12_15 3 11 16 14 A_11_12_14_16 4 11 17 14 A_11_12_14_17 4 12 12 11 A_11_12 1 12 14 12 A_11_12_14 2 12 15 12 A_11_12_15 2 12 16 14 A_11_12_14_16 3 12 17 14 A_11_12_14_17 3 13 13 11 A_11_13 1 14 14 12 A_11_12_14 1 14 16 14 A_11_12_14_16 2 14 17 14 A_11_12_14_17 2 15 15 12 A_11_12_15 1 16 16 14 A_11_12_14_16 1 17 17 14 A_11_12_14_17 1 21 21 0 B_21 1 21 22 21 B_21_22 2 21 23 21 B_21_23 2 22 22 21 B_21_22 1 23 23 21 B_21_23 1

SELECT CONNECT_BY_ROOT -- précise la colonne qui sera le parent t1.id1 AS root_parent_id -- le parent principal , t1.id1 AS child_id -- Les enfants du parent principal , NVL(t1.parent_id1, 0) AS parent_id1 -- Le parent direct de l'enfant sélectionné , t1.code AS code , LEVEL AS level_parent_child -- La distance entre le parent principal et l'enfant sélectionné FROM table_connect_by t1 START WITH t1.id1 = 12 -- précise un parent principal avec lequel commencer la sélection des sous enfants CONNECT BY PRIOR t1.id1 = t1.parent_id1;
Résultat de la requête : 12 12 11 A_11_12 1 12 14 12 A_11_12_14 2 12 15 12 A_11_12_15 2 12 16 14 A_11_12_14_16 3 12 17 14 A_11_12_14_17 3

SELECT CONNECT_BY_ROOT -- précise la colonne qui sera le parent t1.id1 AS root_parent_id -- le parent principal , t1.id1 AS child_id -- Les enfants du parent principal , NVL(t1.parent_id1, 0) AS parent_id1 -- Le parent direct de l'enfant sélectionné , t1.code AS code , LEVEL AS level_parent_child -- La distance entre le parent principal et l'enfant sélectionné , SYS_CONNECT_BY_PATH(t1.parent_id1, '/') AS path_parent , SYS_CONNECT_BY_PATH(t1.id1, '/') AS path_child FROM table_connect_by t1 START WITH t1.id1 = 12 -- précise un parent principal avec lequel commencer la sélection des sous enfants CONNECT BY PRIOR t1.id1 = t1.parent_id1;
Résultat de la requête : 12 12 11 A_11_12 1 /11 /12 12 14 12 A_11_12_14 2 /11/12 /12/14 12 15 12 A_11_12_15 2 /11/12 /12/15 12 16 14 A_11_12_14_16 3 /11/12/14 /12/14/16 12 17 14 A_11_12_14_17 3 /11/12/14 /12/14/17

SELECT CONNECT_BY_ROOT -- précise la colonne qui sera le parent t1.id1 AS root_parent_id -- le parent principal , t1.id1 AS child_id -- Les enfants du parent principal , NVL(t1.parent_id1, 0) AS parent_id1 -- Le parent direct de l'enfant sélectionné , t1.code AS code , LEVEL AS level_parent_child -- La distance entre le parent principal et l'enfant sélectionné FROM table_connect_by t1 WHERE LEVEL <= 2 AND t1.id1 != 14 -- ajouter des conditions START WITH t1.id1 = 12 -- précise un parent principal avec lequel commencer la sélection des sous enfants CONNECT BY PRIOR t1.id1 = t1.parent_id1;
Résultat de la requête : 12 12 11 A_11_12 1 12 15 12 A_11_12_15 2