Solved

Sort a query based on a hierarchical table

Posted on 2008-10-15
3
443 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
  • 2
3 Comments
 
LVL 73

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 73

Expert Comment

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

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

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…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to take different types of Oracle backups using RMAN.

930 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now