title: Recursive SQL Query with order tree
body: <p>I have a problem to order a query as a tree as this </p>
  
  <pre><code>WITH UtHierarchy
  AS (
      SELECT etabid
          ,ut
          ,utlib
          ,parenteut
          ,0 AS LEVEL
          ,ut AS root
      FROM RUT
      WHERE etabid = 1
          AND parenteut IS NULL
  
      UNION ALL
  
      SELECT RUT.etabid
          ,RUT.ut
          ,RUT.utlib
          ,RUT.parenteut
          ,LEVEL + 1 AS LEVEL
          ,RUT.parenteut AS root
      FROM RUT
      INNER JOIN UtHierarchy uh ON uh.ut = rut.parenteut
      WHERE RUT.ETABID = 1
      )
  SELECT *
  FROM UtHierarchy
  ORDER BY root
  </code></pre>
  
  <p>I need to have the following tree :</p>
  
  <pre>
  UT Root
  UT Root
  -- UT level 1
  UT Root
  -- UT level 1
  -- -- UT level 2
  UT Root
  </pre>
  
  <p>This is working as intended for level 0 or 1, but for higher level it s broken. I try to select in root column the 'level 0' parent to order by root and ut, but after some time on this problem, I can't :(</p>
  
  <p>How to resolve this ?</p>
  
  <p>Thanks for your help.</p>
  
  <p><strong>EDIT</strong> : Thank you for editing with sql colors :)
  I've seen the solution for the topmost level but user has deleted his post.</p>
  
  <pre><code>WITH UtHierarchy 
  AS (
    SELECT etabid
    ,ut
    ,utlib
    ,parenteut,
    0 as profondeur,
    ut as root
    FROM RUT 
    where etabid = 278
    and parenteut is null
    UNION  ALL
    SELECT RUT.etabid
    , RUT.ut
    , RUT.utlib
    , RUT.parenteut
    , profondeur + 1 as profondeur
    , root as root 
    FROM RUT 
    inner join UtHierarchy uh on uh.ut = rut.parenteut
    where RUT.ETABID = 278
  )
  select ut, parenteut, profondeur, root 
  from UtHierarchy
  order by root
  </code></pre>
  
  <p>but it's not working too</p>
  
  <p>Here an example with true data </p>
  
  <pre>
  ut  parenteutlevel  root
  10  1   1   1
  11  1   1   1
  12  1   1   1
  13  1   1   1
  14  1   1   1
  130 13  2   1
  131 13  2   1
  132 13  2   1
  133 13  2   1
  134 13  2   1
  135 13  2   1
  136 13  2   1
  120 12  2   1
  121 12  2   1
  122 12  2   1
  110 11  2   1
  111 11  2   1
  112 11  2   1
  113 11  2   1
  114 11  2   1
  115 11  2   1
  116 11  2   1
  101 10  2   1
  102 10  2   1
  103 10  2   1
  104 10  2   1
  105 10  2   1
  106 10  2   1
  107 10  2   1
  1       0   1
  </pre>
  
  <p>As you can see it is not the good structure.
  I need a tree as this :</p>
  
  <pre>
  ut  parenteutlevel  root
  1       0   1
  10  1   1   1
  101 10  2   1
  102 10  2   1
  103 10  2   1
  104 10  2   1
  105 10  2   1
  106 10  2   1
  107 10  2   1
  11  1   1   1
  110 11  2   1
  111 11  2   1
  112 11  2   1
  113 11  2   1
  114 11  2   1
  115 11  2   1
  116 11  2   1
  12  1   1   1
  120 12  2   1
  121 12  2   1
  122 12  2   1
  13  1   1   1
  130 13  2   1
  131 13  2   1
  132 13  2   1
  133 13  2   1
  134 13  2   1
  135 13  2   1
  136 13  2   1
  14  1   1   1    
  </pre>