recursive query Sort

Question

I have a Standard table, which sotres parent, child category relationship...like this.

id, parent, catName, sort

And I use the following query to create a recursive tree

;WITH cte AS (
    SELECT 0 AS lvl, id, catName, parent,levels,sort,
        CAST(id AS VARCHAR(128)) AS path
    FROM CategoriesMap WHERE parent =0
    UNION ALL
    SELECT p.lvl + 1, c.id, c.catName, c.parent,c.levels,c.sort,
        CAST(p.path + '_' + CAST(c.id AS VARCHAR) AS VARCHAR(128))
    FROM CategoriesMap c
    INNER JOIN cte p ON p.id = c.parent
)
SELECT 
    id, 
    catName AS catName, 
    lvl,
    levels,
    path,
    parent,
    sort
FROM cte 
ORDER BY path

And the output is like this Image:

Look for the Row with value ASP.NET & CLASSIC ASP, these are the last leaf(children) for the technology > Software (parents), I want to sort the LAST CHILDREN of any given parent (last parent). I can have multiple parents for a given node (last child) & all I care about is sort the LAST Children (leaf) using the "Sort" column.

so basicly "Classic Asp" shoud be before "Asp.Net" (Last Column is SORT column in my image).

My query is fine, it returns the results as expected...only challenege is i want to SORT the last NODE using the SORT column in table, last node can have 3 or 4 children which I want to sort, all nodes above the last node are its parents (which are in correct order already).

I want output like this.... Internet > ISP's > CableVision (1) : Verizon (2) as you can see CableVision & Verizon have Sort Value of 1 & then 2, Now lets say we have Shopping > Coupons > Macys(0) : Sears (2), same thing....I want Macys & Sears to be sorted...and its pretty obvious their parents are Shopping > Coupons.

@Richard aka cyberkiwi, after applying your code, my sorting for Categories table is very random. output is below

Answer 1

This SQL Fiddle should give you what you need. The trick really is when you mix leaves with branches. In my solution, leaves ALWAYS appear before branches, and within the leaves (even when inter-mixed with branches), they are sorted by the sort column of course.

DDL

create table CategoriesMap(
    id int, parent int, catname varchar(20), sort int);
insert CategoriesMap select
    1, 0, 'Activities', null union all select
    2, 0, 'Property', null union all select
    3, 2, 'For rent', null union all select
    4, 2, 'For sale', null union all select
    12, 0, 'Technology', 3 union all select
    15, 12, 'Hardware', null union all select
    21, 12, 'Phones', null union all select
    22, 15, 'Computers', null union all select
    18, 12, 'Software', null union all select
    19, 18, 'Asp.net', 2 union all select
    20, 18, 'SQL', 3 union all select
    23, 18, 'Php', 4 union all select
    24, 18, 'Classic ASP', 1;

Query

;WITH leaves AS (
    SELECT A.id
      FROM CategoriesMap A
 LEFT JOIN CategoriesMap B ON A.id=B.parent
     WHERE B.id is null
)
,cte AS (
    SELECT 0 AS lvl, id, catName, parent,sort,
           CAST(id AS VARCHAR(MAX)) AS path,
           '/'+CAST(id AS VARCHAR(MAX))+'/' AS hier
      FROM CategoriesMap
     WHERE parent =0
 UNION ALL
    SELECT p.lvl + 1, c.id, c.catName, c.parent,c.sort,
           p.path + '_' + CAST(c.id AS VARCHAR(MAX)),
           p.hier + CAST(c.id AS VARCHAR(MAX)) + '/'
      FROM CategoriesMap c
      JOIN cte p ON p.id = c.parent
)
    SELECT c.id,
           c.catName,
           c.lvl,
           --levels,
           c.path,
           --c.hier,
           c.parent,
           c.sort
      FROM cte c
 LEFT JOIN leaves l on l.id=c.id
  ORDER BY CASE WHEN l.id is null
                then cast(hier as hierarchyid)
                else cast(hier as hierarchyid).GetAncestor(1)
                END,
           CASE WHEN l.id is null then 0 else 1 end,
           sort

Answer 2

Delaying the calculation of the path for one level, so the final result set has the parent path (ppath) available:

;WITH cte AS (
    SELECT 0 AS lvl, id, catName, parent,levels,sort,
        CAST('' AS VARCHAR(128)) AS ppath
    FROM CategoriesMap WHERE parent =0
    UNION ALL
    SELECT p.lvl + 1, c.id, c.catName, c.parent,c.levels,c.sort,
        CAST(p.ppath + '_' + CAST(p.id AS VARCHAR) AS VARCHAR(128))
    FROM CategoriesMap c
    INNER JOIN cte p ON p.id = c.parent
)
SELECT 
    id, 
    catName, 
    lvl,
    levels,
    CAST(ppath + '_' + CAST(id AS VARCHAR) AS VARCHAR(128)) AS path,
    parent,
    sort
FROM cte
ORDER BY 
    CASE WHEN sort IS NULL
           THEN path
           ELSE ppath
    END
  , sort ;

Not really sure why the above gives error. This will not:

ORDER BY 
    CASE WHEN sort IS NULL
      THEN CAST(ppath + '_' + CAST(id AS VARCHAR) AS VARCHAR(128))
      ELSE ppath
    END
  , sort ;