Adding sorting override to tree data with CTE

Question

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.

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)

My query to get the entire catalog tree is:

;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 & CatalogLevel
        select Catalogs.CatalogID, Catalogs.[Name], 
            Catalogs.ParentCatalogID, CL.CatalogLevel + 1, 
            CL.CatalogPath + ' > ' + Catalogs.[Name] as CatalogPath, Catalogs.[Index]
        from Catalogs
        inner join CatalogList as CL on CL.CatalogID = Catalogs.ParentCatalogID
        where Catalogs.ParentCatalogID > 0
    )
select CatalogList.*
from CatalogList
order by CatalogPath

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 ROW_NUMBER() OVER(PARTITION BY...) but haven't been able to make it work.

user_1570000 · Answer

You were on the right path with row_number()

Example

Declare @YourTable Table ([CatalogID] int,[Name] varchar(50),[ParentCatalogID] int,[Index] int)  
Insert Into @YourTable Values 
 (1661,'Canada',0,0)
,(1663,'All Provinces',1661,0)
,(1665,'AG Install & Leasing',1663,0)
,(1666,'Canada Multi-Use',1663,0)
,(1667,'Construnction & Forestry',1663,1)
,(1668,'Turf',1663,'')
,(1664,'Quebec Only',1661,0)

;with cteP as (
      Select CatalogID
            ,[Name]
            ,ParentCatalogID
            ,CatalogLevel = 1
            ,CatalogPath = convert(varchar(500),[Name])
            ,[Index]
            ,Seq = convert(varchar(500),concat('',10000+row_number() over (partition by ParentCatalogID order by [Index] desc,[Name])))
      From   @YourTable
      Where  ParentCatalogID =0
      Union  All
      Select r.CatalogID
            ,r.[Name]
            ,r.ParentCatalogID
            ,p.CatalogLevel + 1
            ,CatalogPath = convert(varchar(500),concat(p.CatalogPath,' > ',r.[Name]))
            ,r.[Index]
            ,Seq = convert(varchar(500),concat(p.Seq,concat('\',10000+row_number() over (partition by r.ParentCatalogID  order by r.[Index] desc,r.[Name]))))
      From   @YourTable r
      Join   cteP p on r.ParentCatalogID  = p.CatalogID
)
Select *
 From  cteP 
 Order By Seq

Returns

Adding sorting override to tree data with CTE

1 Answers