kostja7 kostja7 - 1 year ago 39
SQL Question

Oracle custom order based on previous id field

I have a table with a previous id and want to sort the output based on id_prev. Null must be the first value.

example image

Table

id | id_prev | data
------------------
1 | 2 | foo
2 | 4 | foo
3 | 1 | foobar
4 | null | fooo
10 | 3 | fo
8 | 10 | fo


Result

id | id_prev | sort | data
------------------
4 | null | 1 | fooo
2 | 4 | 2 | foo
1 | 2 | 3 | foo
3 | 1 | 4 | foobar
10 | 3 | 5 | fo
8 | 10 | 6 | fo

Answer Source

CONNECT BY PRIOR

select id,id_prev,level,data 
from TABLE_NAME
start with id_prev is null
connect by prior  id=id_prev
order by level;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download