title: Adding sorting override to tree data with CTE
body: <p>I have a catalog tree that sorts on the catalog name (actually the full catalog path - "root > sub1 > sub2 > my catalog"). I added an "Index" column to override the sorting within a catalog. I was able to make it work when viewing the immediate decedents of a single catalog but I can't get it to work when viewing the entire catalog tree. </p>
  
  <pre><code>dbo.Catalogs
  - CatalogID (int,identity,key)
  - Name
  - ParentCatalogID (int - 0 for root level catalogs)
  - Index (int=0, new field to override alpha sort - higher numbers should take priority)
  </code></pre>
  
  <p>My query to get the entire catalog tree is:</p>
  
  <pre><code>;with CatalogList as
      ( 
          -- top level catalogs
          select Catalogs.CatalogID, [Name], 
              ParentCatalogID, 1 as CatalogLevel, 
              cast([Name] as varchar(max)) as CatalogPath, Catalogs.[Index]
          from Catalogs
          where ParentCatalogID = 0           
  
          union all
  
          -- sub catalogs, building CatalogPath &amp; CatalogLevel
          select Catalogs.CatalogID, Catalogs.[Name], 
              Catalogs.ParentCatalogID, CL.CatalogLevel + 1, 
              CL.CatalogPath + ' &gt; ' + Catalogs.[Name] as CatalogPath, Catalogs.[Index]
          from Catalogs
          inner join CatalogList as CL on CL.CatalogID = Catalogs.ParentCatalogID
          where Catalogs.ParentCatalogID &gt; 0
      )
  select CatalogList.*
  from CatalogList
  order by CatalogPath
  </code></pre>
  
  <p>Currently this only works for alpha sorting. The output below would need to have CatalogID 1667 come before 1665 because the Index is higher. I tried looking at <code>ROW_NUMBER() OVER(PARTITION BY...)</code> but haven't been able to make it work.</p>
  
  <p><a href="https://i.stack.imgur.com/2KSGy.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/2KSGy.png" alt="enter image description here"></a></p>