Hierarchical Data

Sorting

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

  1. does not exist in values under T.KEY
  2. 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.




Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

  • [url=http://www.sqlsnippets.com/en/topic-11736.html]SQL Snippets: Hierarchical Data - Sorting[/url]

To link to this page in HTML documents or Blogger comments cut and paste this code.

  • <a href="http://www.sqlsnippets.com/en/topic-11736.html">SQL Snippets: Hierarchical Data - Sorting</a>

To link to this page in other web sites use the following values.

  • Link Text : SQL Snippets: Hierarchical Data - Sorting
  • URL (href): http://www.sqlsnippets.com/en/topic-11736.html