Given that the data is stated to be a "treeview" and in the followup review that it needs to be "reverse engineered", we can make some assumptions.
If we assume that the data we have are the output of a connect by query like this:
select
rownum as linenumber
, level as mylevel
, id
from source_data
start with parent_id is null
connect by parent_id = prior id
order siblings by id;
Then we can assume that a parent is the
last row
before the current row that has a
smaller level.
That enables us to find the parent id for example like this:
with rws as (
select 1 LineNumber, 1 mylevel, 10 id from dual
union all
select 2 , 2 , 11 from dual
union all
select 3 , 3 , 13 from dual
union all
select 4 , 3 , 14 from dual
union all
select 5 , 4 , 15 from dual
union all
select 6 , 2 , 20 from dual
union all
select 7 , 2 , 30 from dual
union all
select 8 , 3 , 31 from dual
union all
select 9 , 4 , 33 from dual
union all
select 10 , 3 , 32 from dual
union all
select 11 , 3 , 34 from dual
union all
select 12 , 4 , 35 from dual
union all
select 13 , 5 , 36 from dual
)
select
rws.id
, (
select max(id) keep (dense_rank last order by linenumber)
from rws prev_parents
where prev_parents.linenumber < rws.linenumber
and prev_parents.mylevel < rws.mylevel
) as parent_id
from rws;
(I tried if I could do it with LAST_VALUE IGNORE NULLS, but couldn't. MATCH_RECOGNIZE isn't available on 11g. MODEL clause perhaps, but I couldn't think of way without spending a lot of time on it. The scalar subquery works, but if it's a large dataset, it'll probably not be fast.)
We can check if the reverse engineering was correct:
with rws as (
select 1 LineNumber, 1 mylevel, 10 id from dual
union all
select 2 , 2 , 11 from dual
union all
select 3 , 3 , 13 from dual
union all
select 4 , 3 , 14 from dual
union all
select 5 , 4 , 15 from dual
union all
select 6 , 2 , 20 from dual
union all
select 7 , 2 , 30 from dual
union all
select 8 , 3 , 31 from dual
union all
select 9 , 4 , 33 from dual
union all
select 10 , 3 , 32 from dual
union all
select 11 , 3 , 34 from dual
union all
select 12 , 4 , 35 from dual
union all
select 13 , 5 , 36 from dual
),
source_data as (
-- reverse engineered
select
rws.id
, (
select max(id) keep (dense_rank last order by linenumber)
from rws prev_parents
where prev_parents.linenumber < rws.linenumber
and prev_parents.mylevel < rws.mylevel
) as parent_id
from rws
)
select
rownum as linenumber
, level as mylevel
, id
from source_data
start with parent_id is null
connect by parent_id = prior id
order siblings by id;
Looks to me like it gives the same output ;-)