Lets add some more nodes and change one node name to start with a different letter (this is where we come back to “Node 2.2” which we initially left out):
INSERT INTO TreeNode VALUES(14, 7, 'Node 2.2'); INSERT INTO TreeNode VALUES(15, 1, 'Node 0'); INSERT INTO TreeNode VALUES(16, 15, 'Node 0.1'); INSERT INTO TreeNode VALUES(17, 15, 'Node 0.2'); UPDATE TreeNode SET Label='ANode 1.2.1' WHERE Id=5; COMMIT;
When using the same query as above, the result we get is
LEVEL ID NODE
---------- ---------- --------------------------
1 1 Root Node
2 2 Node 1
3 3 Node 1.1
3 4 Node 1.2
4 5 ANode 1.2.1
5 6 Node 1.2.1.1
2 7 Node 2
3 8 Node 2.1
3 9 Node 2.3
3 10 Node 2.4
3 15 Node 2.2
2 11 Node 3
2 12 Node 4
3 13 Node 4.1
2 19 Node 0
3 20 Node 0.1
3 21 Node 0.2
Note that Node 2.2″ and “Node 0” are properly located with respect to their parent, but we would probably expect that “Node 0” is shown before “Node 1” and also that “Node 2.2” is shown between “Node 2.1” and “Node 2.3”. So, we need to define the order between the nodes on each level. We can not simply use ORDER BY, since this would affect the overall result set. Generally, do not use plain ORDER BY or GROUP BY together with hierarchical queries – they will affect the overall result set and destroy the hierarchical order created by CONNECT BY:
SELECT LEVEL, Id, substr( LPAD (' ', 3 * (LEVEL - 1)) || Label, 0, 30) Node
FROM TreeNode
START WITH Parent = 0
CONNECT BY PRIOR Id = Parent
ORDER BY Label;
LEVEL ID NODE
---------- ---------- --------------------------
4 5 ANode 1.2.1
2 19 Node 0
3 20 Node 0.1
3 21 Node 0.2
2 2 Node 1
3 3 Node 1.1
3 4 Node 1.2
5 6 Node 1.2.1.1
2 7 Node 2
3 8 Node 2.1
3 15 Node 2.2
3 9 Node 2.3
3 10 Node 2.4
2 11 Node 3
2 12 Node 4
3 13 Node 4.1
1 1 Root Node
As we can see, both “ANode 1.2.1” and “Root Node” are completely messed up and located at the wrong position – this is caused by the ORDER BY which orders the result set as a whole (after the CONNECT BY has been applied).
However, there is the ORDER SIBLINGS BY clause to sort the nodes on each level:
SELECT LEVEL, Id, substr( LPAD (' ', 3 * (LEVEL - 1)) || Label, 0, 30) Node
FROM TreeNode
START WITH Parent = 0
CONNECT BY PRIOR Id = Parent
ORDER SIBLINGS BY Label;
This finally returns the expected tree, with the nodes sorted by their label within each level:
LEVEL ID NODE
---------- ---------- --------------------------
1 1 Root Node
2 19 Node 0
3 20 Node 0.1
3 21 Node 0.2
2 2 Node 1
3 3 Node 1.1
3 4 Node 1.2
4 5 ANode 1.2.1
5 6 Node 1.2.1.1
2 7 Node 2
3 8 Node 2.1
3 15 Node 2.2
3 9 Node 2.3
3 10 Node 2.4
2 11 Node 3
2 12 Node 4
3 13 Node 4.1