Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Ordering of Hierarchical trees by level 1 id

Ordering of Hierarchical trees by level 1 id

From: praveen <prsingh_at_ipolicynet.com>
Date: 13 Jul 2004 03:16:34 -0700
Message-ID: <17e9bade.0407130216.30a20a38@posting.google.com>


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:16:34 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US