Category Archives: sql

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