Archive December 2010

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