Order a select

[sgfbio]

I try to order the next codes of the column "codbobn2" (varchar2(7)). If i do the simple
"order by codbobn2 asc" I obtain:

9602915
9703046 --> mother
9703995
9783046 --> child of 9703046
9793046 --> child of 9703046

But I want to order like this, because the codes whose 3rd position <> '0' are
childs of the code whose 3rd position = '0'.

9602915
9703046 --> mother
9783046 --> child of 9703046
9793046 --> child of 9703046
9703995

I thougth to replace the 3er position of all values = '0' and after order, or something
like this but I don't know how can do this?

[rski]

order by substr(codbobn2,3)
 

[sgfbio]

Thank you for your quick response but perhaps I haven't explain well what I want to obtain: I have to order in 2 ways at the same time:

* the codes by codes (or codbobn2) asc
* but inside this first sort, a second sort by code "mother" (substr(codbobn2,3)=0)
and childs (same code as the mother but the substr(codbobn2,3) <> 0).

I tried your answer but the result it isn't what I wanted:

9602915
9703046
9703995
9793046

(and the 9783046 "disappears")

[rski]

That

select * from example order by substr(codbobn2,4), substr(codbobn2,3);
 

returns

9602915   
9703046   
9783046   
9793046   
9703995 
 

I've checked it. Is that what you want?

[OraMaster]

The data you posted is not sufficient. Try to post some more data and also explain logically which codes you want to be the childs of another code which have zero at 3rd position.

[sgfbio]

Sorry....

Thank you, it's what I wanted

[amitpatel66]

Can you please confirm that the third digit of PARENT records will always be 0?