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

All we need is an easy explanation of the problem, so here it is.

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.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

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

Method 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.

Method 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

Method 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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply