Order by parent

Question

I have a STUDENT table:

• ID NUMBER(8)
• PARENT_ID NUMBER(8)

with data:

54, 99
50, null
5, 54,
99, 50

How can I query them in this order?

50 <-- first, because it has no parent, so it's root
99 <-- second, because it's parent is the root item
54 <-- third, because it's parent is the second item
5  <-- forth, because it's parent is the third item

Answer 1

You need a CONNECT BY clause.

See the SQL Fiddle.