Solved

Sort a query based on a hierarchical table

Posted on 2008-10-15
3
446 Views
Last Modified: 2013-12-19
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
Comment
Question by:Aludaan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 22727905
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
 

Author Closing Comment

by:Aludaan
ID: 31506590
That was perfect sdstuber. You rock.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 22737495
glad I could help!
0

Featured Post

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example, show how to take different types of Oracle backups using RMAN.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question