select CONNECT_BY_ROOT(dept_no),parent_dept from test where CONNECT_BY_ISLEAF=1 start with dept_no='3013010000053' connect by dept_no= prior parent_dept;
with t as ( select '3013010000053' as dept_no, '301301' as parent_dept, '03' as dept_level from dual union all select '301301', '3013', '02' from dual union all select '3013', '30', '01' from dual ) select distinct first_value(dept_no) over(order by dept_level desc) as dept_no, first_value(parent_dept) over(order by dept_level) as parent_dept from t;
( select '3013010000053' as dept_no, '301301' as parent_dept, '03' as dept_level from dual
union all select '301301', '3013', '02' from dual
union all select '3013', '30', '01' from dual )
select distinct
first_value(dept_no) over(order by dept_level desc) as dept_no,
first_value(parent_dept) over(order by dept_level) as parent_dept
from t;