?
Solved

Sort a query based on a hierarchical table

Posted on 2008-10-15
3
Medium Priority
?
450 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 2000 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

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…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Suggested Courses

777 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