Tree Queries

Is it possible to order a Tree Query based on a column and still maintain the tree structure.

I need to retrieve data from a table which contains a parent/child id field.

This is no problem with the tree query but I now need to order the parent rows based on a separate field.

An example may explain a little better!...

This is a sample output with the standard tree query...

ID	PARENT		DESC		ORDERFIELD
1	0		Scope1		100
2	1		Scope11	
3	0		Scope2		300
4	3		Scope22	
5	4		Scope221	
6	0		Scope3		200
7	6		Scope31	

What I need is...

ID	PARENT		DESC		ORDERFIELD
1	0		Scope1		100
2	1		Scope11	
6	0		Scope3		200
7	6		Scope31	
3	0		Scope2		300
4	3		Scope22	
5	4		Scope221

Here is the SQL I am currently using if it's of any use...

SELECT TBLSCOPE.*, TBLAPPROVALSTATUS.STRASTDESCRIPTION FROM TBLSCOPE, TBLAPPROVALSTATUS,
(SELECT ROWNUM AS TREEROW, LEVEL AS TREELEVEL, LNGSURN FROM TBLSCOPE START WITH LNGPARENTURN = 0 AND LNGCPURN = 466 CONNECT BY PRIOR LNGSURN=LNGPARENTURN) TREE
WHERE TBLSCOPE.LNGASTURN = TBLAPPROVALSTATUS.LNGASTURN (+) AND TBLSCOPE.LNGSURN = TREE.LNGSURN
ORDER BY TREE.TREEROW I am new to Oracle so any help is greatly appreciated

Darren Lewis
lewisd_at_nospamtechnosys.co.uk
Remove the nospam to respond. Received on Wed May 05 1999 - 09:12:40 CDT