• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 454
  • Last Modified:

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
##
## 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

Open in new window

0
Aludaan
Asked:
Aludaan
  • 2
1 Solution
 
sdstuberCommented:
use the SIBLINGS keyword to sort hierarchical data
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
ORDER SIBLINGS BY nl.name

Open in new window

0
 
AludaanAuthor Commented:
That was perfect sdstuber. You rock.
0
 
sdstuberCommented:
glad I could help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now