Since START WITH and CONNECT BY apply a hierarchical sorting scheme to your data, you should generally not use any features that apply other sorting schemes, such as ORDER BY or GROUP BY, in your hierarchical queries. Doing so would negate the need for START WITH and CONNECT BY in the first place.
For example, given data with the following hierarchies
KEY_INDENTED --------------- nls demo mesg server bin config log ctx admin data delx enlx eslx mig
the ORDER BY clause in the hierarchical query on the left below destroys the hierarchical order. It yields the same results as if CONNECT BY was not used at all.
Hierarchical Query |
Regular Query |
|---|---|
select key from t start with parent_key is null connect by parent_key = prior key ORDER BY NAME ; KEY ---------- admin bin config ctx data demo delx mesg enlx eslx log mig nls server |
select key from t -- -- -- -- ORDER BY NAME ; KEY ---------- admin bin config ctx data demo delx mesg enlx eslx log mig nls server |
ORDER SIBLINGS BY
Unlike ORDER BY and GROUP_BY, the ORDER SIBLINGS BY clause will not destroy the hierarchical ordering of queries. It allows you to control the sort order of all rows with the same parent (aka "siblings"). The following examples show how ORDER SIBLINGS BY can be used to sort siblings in ascending and descending order respectively.
Ascending Siblings |
Descending Siblings |
|---|---|
select
lpad(' ', level-1) || key
as key_indented
from
t
start with
parent_key is null
connect by
parent_key = prior key
ORDER SIBLINGS BY
KEY ASC
;
KEY_INDENTED --------------- nls demo mesg server bin config log ctx admin data delx enlx eslx mig |
select
lpad(' ', level-1) || key
as key_indented
from
t
start with
parent_key is null
connect by
parent_key = prior key
ORDER SIBLINGS BY
KEY DESC
;
KEY_INDENTED --------------- server ctx mig data eslx enlx delx admin config log bin nls mesg demo |
Oracle 8i and Earlier
The ORDER SIBLINGS BY clause is only available in Oracle version 9i or greater. For earlier versions a custom, recursive PL/SQL function can be used in place of ORDER SIBLINGS BY.
------------------------------------------------------------ -- Note: -- -- This function is only for demonstration purposes. -- In a real application more robust code would be needed -- to guard against things like separator characters -- appearing in KEY values, hierarchical loops in the data, -- etc. ------------------------------------------------------------ create or replace function KEY_PATH ( p_key t.key%type , p_separator varchar2 default '/' ) return varchar2 is v_parent_key t.parent_key%type ; v_key t.key%type ; begin select parent_key, key into v_parent_key, v_key from t where key = p_key ; if v_parent_key is null then return ( p_separator || v_key ); else return ( KEY_PATH( v_parent_key, p_separator ) || p_separator || v_key ); end if; exception when no_data_found then return( null ); end; / show errors No errors.
Ascending Siblings |
Descending Siblings |
|---|---|
select
lpad(' ', level-1) || key
as key_indented
from
t
start with
parent_key is null
connect by
parent_key = prior key
ORDER BY
KEY_PATH( KEY, '/' )
ASC
;
KEY_INDENTED --------------- nls demo mesg server bin config log ctx admin data delx enlx eslx mig |
select
lpad(' ', level-1) || key
as key_indented
from
t
start with
parent_key is null
connect by
parent_key = prior key
ORDER BY
RPAD( KEY_PATH( KEY, '/' ), 50, '~' )
DESC
;
KEY_INDENTED --------------- server ctx mig data eslx enlx delx admin config log bin nls mesg demo |
Gotchas
KEY_PATH's p_separator character should be a character that
- does not exist in values under T.KEY
- sorts lower than all characters that exist in T.KEY
For descending siblings the code
RPAD( KEY_PATH( KEY, '/' ), 50, '~' )
should use a length larger than any possible KEY_PATH value ("50" in this example) and
it should use a padding character that sorts higher than all characters contained in T.KEY
("~" in this example).
Violating these rules can result in incorrectly sorted output.