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!