Hi,

I have an table which describes tree structure:

CREATE TABLE TAB_Tree (
UID INTEGER PRIMARY KEY,
Parent INTEGER REFERENCES TAB_Tree(UID),
D INTEGER,
AM INTEGER
);

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... ie is the "main filter" ((D >= 100)AND(D <= 200)) in right place?
Any comments, suggestions etc welcome.


TIA
ain