--- In firebird-support@yahoogroups.com, "ainpoissee" <ainpoissee@...> wrote:

>
> and I want to load it into memory in preorder order (root first, then each node is immediately followed by it's childs). As the depth of the tree is unknown I can't use ie join approach, but CTE should fit the bill, right?
> Childs at the same level (it's not a binary tree, number of childs at each level is not limited) must be odered by AM field. Root nodes (Parent = NULL) should be ordered first by D then by AM. D is also the main "filter field" ie the table is queryed for a range of D. I have not worked with CTE before so I'd like to get some help as how to set this query up... this is what I come up with:
>
> WITH RECURSIVE
> Tree AS(
> SELECT UID, Parent, D, AM FROM TAB_Tree
> WHERE(D >= 100)AND(D <= 200)
> UNION ALL
> SELECT T.UID, T.Parent, T.D, T.AM FROM Tree T WHERE(Parent = T.UID)
> )
> SELECT UID, Parent, D, AM FROM Tree
> ORDER BY D, AM, Parent NULLS FIRST
>
> But I'm not quite sure is it right...

Well, it isn't right for sure - in case the child having AM smaller than parent it will be sorted before it's parent, not after... can anyone come up with a query which returns tree in pre- or postorder order?


ain