Leaf node queries
A reader posted A dynamic level limiting hierarchical query about Oracle’s hierarchical queries. They wanted to know how to capture only the hierarchy above the level where the first leaf node occurs. They gave me the following hierarchy map as an example:
1 2
+-------------+ +-----------+
| | | |
3 5 4 6
+---------+ +-----------+ +-----+ +------+
| | | | | | | |
7 9 11 13 8 10 12 14
+-----+ +-----+ +--+ +-------+ +-----+
| | | | | | | | |
15 17 19 21 23 27 29 16 18
+---+
|
20 |
You can find the node values and hierarchical level with the following query:
SELECT tt.child_id , LEVEL FROM test_temp tt WHERE CONNECT_BY_ISLEAF = 1 START WITH tt.parent_id IS NULL CONNECT BY PRIOR tt.child_id = tt.parent_id ORDER BY 2; |
We really don’t need the node values to solve the problem. We only need the lowest LEVEL value returned by the query, which is 3. The combination of the MIN and CONNECT_BY_ISLEAF functions let us solve this problem without writing a PL/SQL solution. The subquery returns the lowest level value, which is the first level where a leaf node occurs.
SELECT LPAD(' ', 2*(LEVEL - 1)) || tt.child_id AS child_id FROM test_temp tt WHERE LEVEL <= (SELECT MIN(LEVEL) FROM test_temp tt WHERE CONNECT_BY_ISLEAF = 1 START WITH tt.parent_id IS NULL CONNECT BY PRIOR tt.child_id = tt.parent_id) START WITH tt.parent_id IS NULL CONNECT BY PRIOR tt.child_id = tt.parent_id; |
It returns:
1 2
+-------------+ +-----------+
| | | |
3 5 4 6
+---------+ +-----------+ +-----+ +------+
| | | | | | | |
7 9 11 13 8 10 12 14 |
While I answered the question in a comment originally, it seemed an important trick that should be shared in its own post.
Subscribe via RSS