i want all nodes of level which i node i am passing in start with clause

Question

Suppose following is hierarchy:

I am passing 5, so i am excepting 5,4,2,1 so how can I do it. Thanks in advance.

Answer 1

If you want all ancestors and siblings of 5 then use parent id in start with clause:

/* sample data
with t(id, pid) as (
    select 1, null from dual union all
    select 2,    1 from dual union all
    select 3,    1 from dual union all
    select 4,    2 from dual union all
    select 5,    2 from dual union all
    select 6,    3 from dual ) */ 
select distinct id
  from t connect by prior pid = id
  start with pid = (select pid from t where id = 5)