WITH filter AS (
select
code,
id,
parent_id
from dictionaryitem
where dictionary_id = '...' and
date_begin <= '...' and date_end >= '...'
),
rootSortedHierarchy AS (
select
connect_by_root code as root,
code,
rownum as rwn
from filter
connect by parent_id = prior ID
start with parent_id is null --root tree
order siblings by code --sort in group by code!
),
fullNotSortedHierarchy AS (
select
connect_by_root code as root,
code,
rownum as rwn
from filter
connect by parent_id = prior ID
),
fullSortedHierarchy AS (
select
hr1.root,
hr1.code
from
fullNotSortedHierarchy hr1
inner join rootSortedHierarchy hr2 on hr1.root = hr2.code
inner join rootSortedHierarchy hr3 on hr1.code = hr3.code
ORDER BY
hr2.rwn, --sort root elements in fullHierarchy by rootSortedHierarchy
hr3.rwn --sort inner elements in fullHierarchy by rootSortedHierarchy
)
select root, code from fullSortedHierarchy