SQL - sorting table with parent child relation

We have a table with a parent-child relation and would li开发者_开发百科ke to get it sorted. The sorting criteria is so that when iterating though the result the row matching a parent ID should already be there :

ID   PARENT_ID 
EF01 EF02         // This is wrong as the row EF02 is after and will fail.
EF02    
BB   AA           // here BB < AA
AA   EF01 

The problem is that both key are string, therefore sorting by ID or PARENT_ID is not going to fix the problem.

For Oracle, using hierarchical queries:

 select id, parent_id, level from the_table
 start with parent_id is null
 connect by prior id = parent_id;

I don't have Oracle to test on. This works in SQL Server and I believe it should work in Oracle as well.

with R(id, parent_id, lvl) as
(
  select id,
         parent_id,
         1
  from YourTable
  where parent_id is null
  union all
  select T.id,
         T.parent_id,
         R.lvl + 1
  from YourTable as T
    inner join R
      on T.parent_id = R.id
)
select R.id,
       R.parent_id
from R
order by lvl

There might be other ways to do the same in Oracle.

Any DBMS will NOT guarantee the order the rows are delivered in unless you ask for for the data in a specific order using the "ORDER BY CLAUSE".

In most DBMSes you can specify the data is stored in a particular order using a "CLUSTERING INDEX" on the column you want to order by. However you wont necessarily get the data back in this order unless you specify an "ORDER BY" in your SQL. Furthermore in many DBMSes this is a "best effort" specification for internal reasons (disk space, concurrent units of work etc.) it may not be possible for the DBMS to store the data in the requested order.