Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Ordering of Hierarchical trees by level 1 id
Hi
I have a table which has a parent child relation represented
hierachically. I want to run a query to get all trees ordered within
themselves and also ordered at top level. In other words all trees
should be ordered by the START WITH id.
eg.
TABLE(id, parentId) PK(id)
SELECT parentId, id FROM TABLE CONNECT BY PRIOR parentId = Id START WITH id IN (SELECT id FROM TABLE where some_condition)
The some_condition returns multiple Ids.
By adding DISTINCT in the subquery i can get all the LEVEL 1's
ordered. But can i be sure that the result returned would always be
sorted in the LEVEL 1 order if LEVEL 1 id is PK of table. Or can i use
PK or indexed column in CONNECT BY PRIOR clause also
eg.
SELECT parentId, id FROM TABLE CONNECT BY PRIOR parentId = Id AND id >
-1 START WITH id IN (SELECT DISTINCT id FROM TABLE where
some_condition)
Currently, im getting all trees sorted in order of LEVEL1 'id', i.e. all trees are returned ordered by their START WITH id. But is it gauranteed to return the same ordered result set always? Received on Tue Jul 13 2004 - 05:19:33 CDT