snippetMinor

How can I select rows from a hierarchical query with the lowest level?

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

rowscanthewithlowestquerylevelhierarchicalhowselect

Problem

I have a hierarchical query in Oracle 11gR2 that returns something like this:

Parent (Level 1)

Child (Level 2)

Grandchild (Level 3)

Child (Level 2)

Grandchild (Level 3)

Grandchild (Level 3)

Child (Level 2)

The query I would like to write should get all the rows matching some predicate, for the minimum level; i.e. nearest the parent. For example, if one of the child rows matches the predicate, it should return just that row, irrespective of whether any grandchild rows match. If multiple child rows match, it should return all of them, again irrespective of grandchild rows. If no child rows match, it should return any grandchild rows that match, etc. (In the real system I have a lot more than three levels, and lots more rows per level.)

I assume this is possible with analytic functions, but I'm not sure which one to use, or how to integrate it into my query. I've seen similar problems solved using min (level) keep (dense_rank last order by level), but that doesn't seem to do quite what I want.

Solution

If you have an hierarchical query that produces the whole tree under the root node, that also has a level column computed, you can wrap it in a derived table or cte and use the window aggregate:

WITH query AS
  ( SELECT , level
  -- your query here
  ) ,
cte AS 
  ( SELECT , level,
           MIN(the_level) OVER () AS min_level
    FROM query
    WHERE 
  )
SELECT *
FROM cte
WHERE min_level = level ;

Code Snippets

WITH query AS
  ( SELECT <columns list>, level
  -- your query here
  ) ,
cte AS 
  ( SELECT <columns list>, level,
           MIN(the_level) OVER () AS min_level
    FROM query
    WHERE <conditions>
  )
SELECT *
FROM cte
WHERE min_level = level ;

Context

StackExchange Database Administrators Q#64350, answer score: 3

Revisions (0)

No revisions yet.