Hi all,

I use this CTE to load my tree of hierarchically organized
Departments:

with recursive
dept_tree as (
select dept_id, name, 0 as lvl
from depts where parent_dept_id is null

union all

select d.dept_id, d.name, h.lvl + 1
from depts d
join dept_tree h on d.parent_dept_id = h.dept_id
-- *1*
)
select dept_id, lvl, name
from dept_tree

There is only one "root" department.

My problem is: I would like the levels to be ordered by the NAME
field. However, inserting an ORDER BY UPPER (NAME) at the position I
marked with *1* above doesn't change anything (using D.NAME, H.NAME or
DEPT_TREE.NAME doesn't help either).

Is there anything I can do to get ordered levels?


Best Regards

Stefan