Search code examples
sqloraclehierarchicalconnect-by

Oracle hierarchical selective Query

I'm trying to write a hierarchical Oracle 11g query that would select all nodes from a hierarchy from the top level to the second last level. For the last level or leaf nodes, the query should only select one random node, so that is, not all the leaf nodes for every second last node. So if a second last node has three child nodes, the query should only select one of them with no preference.

I'm ok selecting the entire hierarchy with a connect by clause but I'm finding it difficult to limit the selection for the leaf nodes to just one node for every second last node.

Any ideas?

Thanks

Solution

    1. enumerate nodes on the same level

      row_number() over (partition by level order by whatever)

    2. wrap that query in outer query

      to select only first leaf (don't forget to include all middle elements)