How can I select rows from a hierarchical query with the lowest level?
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 ;
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.