Bill
asked on
Sort a query based on a hierarchical table
I created the query below to generate a tree of nodes/groups. What it's output should look like what is below it.
But I can't seem to figure out a way to sort the information. If I add the order by at the end of the query it will create all the nodes first, then the node groups altogether. I showed a example of the output on the bottom. I can't even get the root level groups to alphabetize, I put them in the "nl.name in ('GMMARS')" in order, but they still come up in a seemingly random order
But I can't seem to figure out a way to sort the information. If I add the order by at the end of the query it will create all the nodes first, then the node groups altogether. I showed a example of the output on the bottom. I can't even get the root level groups to alphabetize, I put them in the "nl.name in ('GMMARS')" in order, but they still come up in a seemingly random order
##
## Query
##
SELECT
lpad(' ',4*(level-1))||case when nl.name=' ' then nn.node_name else 'Group:'||nl.name end
FROM
opc_nodehier_layout nl LEFT JOIN opc_node_names nn ON nl.node_id=nn.node_id
START WITH
nl.name in ('GMMARS','NewMMARS')
CONNECT BY
PRIOR nl.layout_id=nl.parent_id;
##
## Query Output Without Sort
##
NewMMARS
Server 3
Server 1
Server 2
GMMARS
GMMARS_MPUAT
gmmarsat1
gmmarsat2
GMMARS_SPT
gmmars2a
gmmars3a
gmmars4a
gmmars5a
gmmars6a
GMMARS_MPSYS
m1t
gmmars2t
gmmarsst1
gmmarsst2
GMMARS_Prod
##
## Query Output With Sort
##
Server 3
Server 1
Server 2
gmmarsat1
gmmarsat2
gmmars2a
gmmars3a
gmmars4a
gmmars5a
gmmars6a
m1t
gmmars2t
gmmarsst1
gmmarsst2
NewMMARS
GMMARS
GMMARS_MPUAT
GMMARS_SPT
GMMARS_MPSYS
GMMARS_Prod
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
glad I could help!
ASKER