Solved

Sort a query based on a hierarchical table

Posted on 2008-10-15
3
442 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
Comment Utility
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
Comment Utility
That was perfect sdstuber. You rock.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
glad I could help!
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

771 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