title: Ordering recursive result set in SQL Server
body: <p>I am having extreme difficulty constructing a query which returns an XML style hierarchy.</p>
  
  <p>We have a database table which contains a hierarchy of URLs for our website. The table contains the columns: ID, URL, DisplayName, ParentID, ItemOrder</p>
  
  <p>The parent ID forms a recursive relationship between the current item and it's parent. The item should site below it's parent in the hierarchy and it should also be ordered using the item order against items at the same level in the hierarchy.</p>
  
  <p>I have managed to get a recursive query working so it drills down the hierarchy sequentially but I cannot order this by the item order as well.</p>
  
  <p>My current query is below:</p>
  
  <pre><code>WITH Parents AS
  (
  SELECT MenuItemId, URL, ParentItemId, ItemOrder
  FROM CambsMenu
  
  UNION ALL
  
  SELECT si.MenuItemId, si.URL, si.ParentItemId, si.ItemOrder
  FROM CambsMenu si INNER JOIN Parents p
  ON si.ParentItemId = p.MenuItemId
  )
  
  SELECT DISTINCT *
  FROM Parents
  </code></pre>