0

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!

1

Simplified with window functions:

with hier as
    (select   level as the_level, <columns>
      from <tables>
        start with <predicate>
        connect by prior <child> = <parent>
    ), levels as
    (select hier.*, count(*) over (order by the_level) as count_items
      from hier
    )
  select levels.*
    from levels
    where the_level = count_items ;
  • I had to qualify the select * as select hier.*, but other than that your query is great - thanks again! – Allan Lewis May 2 '14 at 13:45
  • 1
    Yeah, thnx. That's an Oracle quirk I had forgotten. – ypercubeᵀᴹ May 2 '14 at 13:57
0

I think I've worked it out:

with hier as
    (select   level as the_level, <columns>
      from <tables>
        start with <predicate>
        connect by prior <child> = <parent>
    ), maxlev as
    (select   the_level as max_level
      from
        (select   the_level, count( * )
          from hier
          group by the_level
          having count( * ) > 1
          order by the_level
        )
      where rownum = 1
    )
  select   hier.*
    from hier, maxlev
    where the_level < max_level;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.