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

Question

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.

• ypercubeᵀᴹ: Does your working hierarchical query has a level column computed?
• Allan Lewis: @ypercube Yes, it does.

Answer 1

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 <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 ;

• ypercubeᵀᴹ: @Allan, I wouldn't use both cte and query in the final select. The cte should be enough.
• Allan Lewis: Thanks - you're absolutely correct. I didn't have "the_level" in the cte query so it was failing otherwise.

Answer 2

You could put the hierarchical query in a subquery and user an analytic function and windowing to figure out the first match. If you table looked like:

create table t42 (id number, parent_id number, flag varchar2(1),
 str varchar2(20));

Then this gets the result you want, I think:

select t.id, t.parent_id, t.flag, t.str
from (
 select t.*, min(lvl) over (partition by flag) as min_lvl
 from (
 select t.*, level as lvl
 from t42 t
 start with parent_id is null
 connect by prior id = parent_id
 ) t
) t
where lvl = min_lvl
and flag = '<something>';

... where flag would really be the column(s) for your predicate(s).

Or you could use rank or dense_rank:

select t.id, t.parent_id, t.flag, t.str
from (
 select t.*, rank() over (partition by flag order by lvl) as rn
 from (
 ...
 ) t
) t
where rn = 1
and flag = '<something>';

SQL Fiddle.

Or a slightly tweaked version based on @ypercube's simplification on DBA:

select t.id, t.parent_id, t.flag, t.str
from (
 select t.*, dense_rank() over (order by lvl) as rn
 from (
 ...
 ) t
 where flag = '<something>'
) t
where rn = 1;

SQL Fiddle.

Answer 3

To my mind, the question is ambiguous: does "the minimum level; i.e. nearest the parent" apply globally (so all results have the same level) or to each sub-tree?

If the minimum level is per sub-tree, you can take advantage of how hierarchical queries work and simply stop traversing descendants when your condition is matched:

create table foo(
 id integer primary key
, parent_id integer references foo
, bar char(1)
);

select 1,null,'A' from dual union all
select 2,1,'A' from dual union all
select 3,2,'B' from dual union all
select 4,1,'B' from dual union all
select 5,4,'B' from dual union all
select 6,4,'A' from dual union all
select 7,1,'B' from dual union all
select 8,7,'A' from dual union all
select 9,8,'A' from dual union all
select 10,9,'B' from dual;

select *
from foo
where bar='B';

ID | PARENT_ID | BAR
-: | --------: | :--
 3 | 2 | B 
 4 | 1 | B 
 5 | 4 | B 
 7 | 1 | B 
10 | 9 | B 

select *
from foo
where bar='B'
start with parent_id is null
connect by parent_id=(prior id) and (prior bar)<>'B';

ID | PARENT_ID | BAR
-: | --------: | :--
 3 | 2 | B 
 4 | 1 | B 
 7 | 1 | B 

dbfiddle here

Notice that the row with id=5 is not in the result set — the connect by drops descendants of id=4.

If the minimum level is global, you need an additional step to strip all results not at the minimum level globally:

with l as ( select foo.*, level lev
 from foo
 where bar='B'
 start with parent_id is null
 connect by parent_id=(prior id) and (prior bar)<>'B'
 order by level )
select * from l where lev=(select lev from l where rownum=1);

ID | PARENT_ID | BAR | LEV
-: | --------: | :-- | --:
 4 | 1 | B | 2
 7 | 1 | B | 2

dbfiddle here

Answer 4

If your hierarchy is just two levels deep, I think this way is short and quick:

SELECT p.* FROM ProductCategory p 
LEFT OUTER JOIN ProductCategory c 
ON p.ProductCategoryID = c.ProductCategoryParentID
WHERE c.ProductCategoryParentID IS NULL

Hope this helps.

• András Váczi: This does work only if the tree is 2 levels deep, at most, right?
• QMaster: Yes you right, I need just this and I forget to write about limits, I'll edit the post, Thanks.