connect by
We have following data table category:
category_id | parent_id | name | sort |
---|---|---|---|
10 | null | véhicule motorisé | |
11 | 10 | quatre roues | 3 |
12 | 10 | sans roues | 1 |
13 | 10 | deux roues | 2 |
The recursive way to query childhood relation ship data :
select level, lpad(‘ ‘,level*5,’ ‘) || t.name
from categry t
start with t.parent_id is null
connect by t.parent_id = prior t.category_id
Result of query:
1 | véhicule motorisé |
2 | quatre roues |
2 | sans roues |
2 | deux roues |
Now we have to sort children with value of column sort
select level, lpad(‘ ‘,level*5,’ ‘) || t.name
from categry t
start with t.parent_id is null
connect by t.parent_id = prior t.category_id
order siblings by t.sort
Result of query:
1 | véhicule motorisé |
2 | sans roues |
2 | deux roues |
2 | quatre roues |