RE: Hierarchal queries and order by siblings

I suggest using SYS_CONNECT_BY_PATH to build a string that contains the desired sort.


order by connect_by_root(BPROD)
, SYS_CONNECT_BY_PATH( cast( BSEQ as Char(20)),':' )


Chris Hiebert
Senior Programmer/Analyst
Disclaimer: Any views or opinions presented are solely those of the author and do not necessarily represent those of the company.

From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Darren Strong
Sent: Tuesday, June 28, 2022 1:55 PM
To: midrange-l General Questions (midrange-l@xxxxxxxxxxxxxxxxxx) <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Hierarchal queries and order by siblings


I'd like to do something like the following, where I can sort by the parent, but then within the parent, sort the siblings, but, this violates SQL syntax. I can choose to sort on one or the other, but not both. Is there a way to do this, or should I use the CTE method when selecting multiple parents?



select

connect_by_root(BPROD) as TOPPARENT,

level as CurLVL,

bchld as Child

from MBM

start with BPROD in (select PROD from PARTLIST)

connect by nocycle prior BCHLD=BPROD

order by connect_by_root(BPROD), siblings by BSEQ