Special ordering

Question

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'

Answer 1

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