CODEWITHSUNDEEP
sqloracle-databasehierarchicalconnect-by
sergiopuy
sergiopuy

Reputation: 43

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

Upvotes: 1

Views: 212

Answers (2)

Tyler
Tyler

Reputation: 185

You can use the new recursive subquery factor feature in 11g, for example:

WITH e(owner,NAME,lv,seq) AS
 (SELECT 'SYS' owner, 'OBJ$' NAME, 1 lv, 1 seq
  FROM   dual
  UNION ALL
  SELECT a.owner,
         a.NAME,
         lv + 1,
         row_number() OVER(PARTITION BY e.name, e.owner ORDER BY 1)
  FROM   dba_dependencies a, e
  WHERE  referenced_name = e.name
  AND    referenced_owner = e.owner
  AND    e.seq = 1)
SELECT * FROM e WHERE seq = 1

Or:

WITH e(owner,NAME,lv,seq) AS
 (SELECT 'SYS' owner, 'OBJ$' NAME, 1 lv, 1 seq
  FROM   dual
  UNION ALL
  SELECT a.owner, a.NAME, lv + 1,a.seq  
  FROM   (SELECT a.*,
                 row_number() OVER(PARTITION BY referenced_name, referenced_owner ORDER BY 1) seq
          FROM   dba_dependencies a) a,
         e
  WHERE  referenced_name = e.name
  AND    referenced_owner = e.owner
  AND    a.seq = 1)
SELECT * FROM e

Upvotes: 0

vav
vav

Reputation: 4694

  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)

Upvotes: 1

Related Questions