Sorting in Heirarchical Query

Sorting in Heirarchical Query

Hierarchical queries are used to created hierarchical tree structured outputs.
For example


select ename,sal
from emp;


The above query displays the employees and its salary.

If we need the output as who's boss is who and their salary then the query should be modified like below.



select level,ename,sal
from emp
start with mgr is null
connect by prior empno=mgr;

In the above query "start with" is used to start the output from the boss,who's doesnt have a manager (ie mgr is null)
"connect by piror" is used to defined the relation between the boss and his siblings.

If the output is to be sorted by salary in each level then we need to modify the query as



select level,ename,sal
from emp
start with mgr is null
connect by prior empno=mgr
order siblings by sal desc;

In the above query "order siblings by" is used to sort the output by salary without distrubing the heirarchical structure.