This question is related to my previous question about hierarchical queries.
I have a hierarchical query in Oracle 11gR2 that returns something like this:
- Level 1
- Level 2
- Level 3
- Level 4
- Level 5
- Level 6
- Level 4
- Level 5
- Level 6
- Level 7
I'd like to design a query to return the rows for levels 1-3, on the basis that there are two siblings at level 4. Or, to put it another way, I'd like to return all the rows in the hierarchy, starting from a given point, until I reach a level with more than one sibling. I'd then like to stop there, excluding the level with multiple siblings.
I've tried putting count
functions in various places in my query, but I can't work it out. This is what I have so far:
select *
from
(select level, count (*) over (partition by level) level_count, <columns>
from <tables>
start with <predicate>
connect by prior <child> = <parent>
)
where level_count = 1;
This works for the case there the length of each branch is the same, but returns extra rows otherwise. In my example above, I want to return only the rows for levels 1-3 (i.e. 3 rows in total) and exclude level 7 because level 4 has two siblings. I hope that makes sense!
Best Answer
Simplified with window functions: