Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Does your working hierarchical query has a level column computed? –  ypercube May 2 at 11:00
    
@ypercube Yes, it does. –  Allan Lewis May 2 at 11:05

2 Answers 2

up vote 1 down vote accepted

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 ;
share|improve this answer
    
@Allan, I wouldn't use both cte and query in the final select. The cte should be enough. –  ypercube May 2 at 12:19
    
Thanks - you're absolutely correct. I didn't have "the_level" in the cte query so it was failing otherwise. –  Allan Lewis May 2 at 12:25

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.