-- Collection of code snippets by Arne Vajhøj 
-- posted to eksperten.dk, usenet and other places (2002-now) 
CREATE TABLE tree (
    id INTEGER,
    name VARCHAR2(50),
    parent INTEGER,
    PRIMARY KEY (id)
);
INSERT INTO tree VALUES (0, 'root', NULL);
INSERT INTO tree VALUES (1, 'A', 0);
INSERT INTO tree VALUES (2, 'B', 0);
INSERT INTO tree VALUES (3, 'AA', 1);
INSERT INTO tree VALUES (4, 'AB', 1);
INSERT INTO tree VALUES (5, 'BA', 2);
INSERT INTO tree VALUES (6, 'BB', 2);
INSERT INTO tree VALUES (7, 'AAA', 3);
INSERT INTO tree VALUES (8, 'AAAA', 7);
SELECT t1.id c,t2.id p
FROM tree t1,tree t2
WHERE t2.id IN (SELECT id FROM tree START WITH id = t1.parent CONNECT BY PRIOR parent = id)
ORDER BY c,p DESC;
