Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a hierarchical query but I don't manage to order it as I want.

I have a column named sequence that indicates how to order it but the data is kinda mixed up and I cant do it properly.

This a lookalike of the information I have:

ID      | Sequence | Parent_ID |   text
145     | 1        |  NULL     |'1. INFORMATION'
146     | 2        |  NULL     |'2. MORE INFORMATION'
147     | 3        |  NULL     |'3. EVEN MORE'
148     | 1        |  147      |'  1. INFORMATION LVL2 FOR PARENT 3'
149     | 2        |  147      |'  2. INFORMATION LVL2 FOR PARENT 3'
150     | 4        |  NULL     |'4. EVEN MORE'
151     | 1        |  146      |'  1. INFORMATION LVL2 FOR PARENT 2'

If I run the query without ordering I end up with information like this:

ID      | Sequence | Parent_ID |   text
148     | 4        |  NULL     |'4. EVEN MORE'
145     | 1        |  NULL     |'1. INFORMATION'
147     | 3        |  NULL     |'3. EVEN MORE'
149     | 1        |  147      |'  1. INFORMATION LVL2 FOR PARENT 3'
150     | 2        |  147      |'  2. INFORMATION LVL2 FOR PARENT 3'
146     | 2        |  NULL     |'2. MORE INFORMATION'
151     | 1        |  146      |'  1. INFORMATION LVL2 FOR PARENT 2'

currently I have in place an ORDER BY id, sequence but that sends the second level item of the parent 146 to the last position looking just like the first set of data.

is there a way to order it to put that lost node 151 under its parent 146 without messing up all the other information?

This is more or less what I need as end result:

ID      | Sequence | Parent_ID |   text
145     | 1        |  NULL     |'1. INFORMATION'
146     | 2        |  NULL     |'2. MORE INFORMATION'
151     | 1        |  146      |'  1. INFORMATION LVL2 FOR PARENT 2'
147     | 3        |  NULL     |'3. EVEN MORE'
148     | 1        |  147      |'  1. INFORMATION LVL2 FOR PARENT 3'
149     | 2        |  147      |'  2. INFORMATION LVL2 FOR PARENT 3'
150     | 4        |  NULL     |'4. EVEN MORE'
share|improve this question
    
Which RDBMS are you using? –  Phil Feb 26 '13 at 16:38
    
Oracle 9i and 10g... I have this same behavior in both versions –  Erxgli Feb 26 '13 at 16:42
1  
Can you post the exact result that you want with your example data? –  Lamak Feb 26 '13 at 16:46
    
In the end I expect to have something like this; the child item with id 151 should go after the parent item 146. –  Erxgli Feb 26 '13 at 20:18
    
Edit your post with it, rather than relying on a craply formatted comment –  Phil Feb 26 '13 at 20:22

1 Answer 1

up vote 4 down vote accepted

It would be helpful if you could post the actual query you're running. Even better would be if you created a SQL Fiddle so that we can play around with your data and your tables.

It appears, however, that you simply want to replace your ORDER BY clause with

ORDER SIBLINGS BY sequence

As an example, if you want to report on the EMP table and list the reports in alphabetical order, here is an example of the ORDER BY SIBLINGS clause

share|improve this answer
    
Yep, that solves the issue. Thanks; this is my first time arround, Next time I'll try to use more propper tools to explain... I already saved the URL to add a SQL Fiddle –  Erxgli Feb 26 '13 at 20:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.