What about

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
)
select dept_id, lvl, name
from dept_tree
order by lvl, UPPER (NAME)

Or did I misunderstand something? I consider CTEs to be similar to regular tables and views in that they should be thought of as an unordered set. It is only the output of the entire statement (i.e. as done above) that sometimes should be ordered.

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Stefan Heymann
Sent: 16. april 2009 12:06
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Common Table Expressions and Sorting

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




------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yahoo! Groups Links