Hierarchical query on a temporary table works but is not ordering results as it should-CodePudding

i have a hierarchical query :

with temp1 as (
select distinct 
    b.ID,
    b.LABEL,
    b.parent_id
from my_table b
where b.PROG_MODIF_ID=:P225_PROG_MODIF
)
,temp2 as (
select distinct 
    b.ID,
    b.LABEL,
    p.id as p_id,
    b.parent_id
from temp1 b
left join temp1 p on p.id=b.parent_id
)
select distinct 
    b.ID,
    b.p_id,
    b.LABEL,
    b.parent_id
from temp2 b

start with b.p_id is null
connect by
prior b.id=b.p_id

the results i get with this query are correct but they are not ordered as need be : meaning every parents with its children below, instead they r ordered randomly even though the "parent-child" link is specified b.id=b.p_id

CodePudding user response：

The resultset from a query has no intrinsic order. If you want to order the records then you need to include an ORDER BY clause

CodePudding user response：

You should be able to simplify it to a single hierarchical query using an ORDER SIBLINGS BY clause:

SELECT DISTINCT
       id,
       label,
       PRIOR id AS p_id,
       parent_id
FROM   my_table
START WITH
       parent_id IS NULL
AND    prog_modif_id = :P225_PROG_MODIF
CONNECT BY
       PRIOR id = parent_id
AND    prog_modif_id = :P225_PROG_MODIF
ORDER SIBLINGS BY
       label

However, without a minimal reproducible example it is impossible to test.