title: Oracle "connect by" - multiple hierarchies
body: <p>We're on Oracle 11g R1. Here's the code:</p>
  
  <pre><code>CREATE TABLE T1 (ID NUMBER, PARENT_ID NUMBER, LEFT_SIBLING_ID NUMBER);
  INSERT INTO T1 VALUES (1,NULL,NULL);
  INSERT INTO T1 VALUES (3,1,NULL);
  INSERT INTO T1 VALUES (2,1,3);
  INSERT INTO T1 VALUES (4,2,NULL);
  INSERT INTO T1 VALUES (5,2,4);
  INSERT INTO T1 VALUES (10,NULL,1);
  INSERT INTO T1 VALUES (12,10,NULL);
  INSERT INTO T1 VALUES (11,10,12);
  </code></pre>
  
  <p>What I would like the result to be is:</p>
  
  <pre><code>ID-Tree
  1
      3
      2
          4
          5
  10
      12
      11
  </code></pre>
  
  <p>The key here is that besides the usual PRIOR ID = PARENT_ID hierarchy, there is another hierarchy based on PRIOR ID = LEFT_SIBLING_ID. The children are sorted in the order of PRIOR ID = LEFT_SIBLING_ID. That is why the 3 is followed by 2, and the 12 by 11, etc. That order is important.</p>
  
  <p>I've drawn a blank on how to do this.</p>
  
  <p>Edit:</p>
  
  <p>More rows to clearly illustrate the ordering problem:</p>
  
  <pre><code>CREATE TABLE T1 (ID NUMBER, PARENT_ID NUMBER, LEFT_SIBLING_ID NUMBER);
  INSERT INTO T1 VALUES (1,NULL,10);
  INSERT INTO T1 VALUES (3,1,NULL);
  INSERT INTO T1 VALUES (2,1,3);
  INSERT INTO T1 VALUES (4,2,NULL);
  INSERT INTO T1 VALUES (5,2,4);
  INSERT INTO T1 VALUES (10,NULL,NULL);
  INSERT INTO T1 VALUES (12,10,NULL);
  INSERT INTO T1 VALUES (7,10,12);
  INSERT INTO T1 VALUES (11,10,7);
  INSERT INTO T1 VALUES (6,1,2);
  INSERT INTO T1 VALUES (13,1,6);
  COMMIT;
  </code></pre>
  
  <p>The result of the query:</p>
  
  <pre><code>select substr('----------', 1, lvl*2-2) || to_char(id) id_tree
  from
   (select SYS_CONNECT_BY_PATH(to_char(id,'009'), ':') sort_path, 
          left_sibling_id, id, parent_id, level lvl
   from t1 
   start with parent_id is null
   connect by prior id = parent_id) q
  start with left_sibling_id is null
  connect by prior id = left_sibling_id 
  and coalesce(parent_id,id) = coalesce(parent_id,id)
  order by case lvl when 1 then sort_path 
                    else substr(sort_path,1,length(sort_path)-4) end,
           level;
  
  ID_TREE                                            
  -------------------------------------------------- 
  1                                                  
  --3                                                
  --2                                                
  --6                                                
  --13                                               
  ----4                                              
  ----5                                              
  10                                                 
  --12                                               
  --7                                                
  --11                                               
  
   11 rows selected
  </code></pre>
  
  <p>Though the siblings are ordered properly (except the top level), they are no longer immediately under their parent.</p>