Рекурсивный CTE с порядком

Вот правильный CTE (только для возрастания)

;WITH CTE AS
(
SELECT  id, id_parent, name_product
        ,HierarchicalPath = CAST('\'+CAST(name_product AS VARCHAR(MAX)) AS VARCHAR(MAX)) 
FROM @tab where id_parent is null -- Starts with the first level

UNION ALL

SELECT  t.id, t.id_parent, t.name_product
        ,HierarchicalPath = CAST(c.HierarchicalPath + '\'+CAST(t.name_product AS VARCHAR(MAX)) AS VARCHAR(MAX))
FROM @tab t INNER JOIN CTE C
ON t.id_parent = C.id
)

select * from cte order by HierarchicalPath

Вот код, который я почти сделал, что хочу. Теперь проблема в том, чтобы заказать по убыванию

declare @tab table(
    id int identity(1,1)
    ,id_parent int
    ,name_product varchar(100)
)

insert into @tab
select null, 'Eletronics'
union all
select 1, 'Televisions'
union all
select 2, 'Tube'
union all
select 2, 'LCD'
union all
select 2, 'Plasma'
union all
select 1, 'Portable Electronic'
union all
select 6, 'MP3 Players'
union all
select 7, 'Flash'
union all
select 6, 'CD Player'
union all
select 6, 'Two Way Radios'

;WITH CTE (id,id_parent,name_product,LEVEL,SORTKEY)AS
(
    SELECT id, id_parent, name_product, 1, CAST(name_product AS VARBINARY(MAX))
    FROM @tab where id_parent is null -- Starts with the first level

    UNION ALL

    SELECT t.id, t.id_parent, t.name_product, C.LEVEL + 1, CAST(C.SORTKEY + CAST(t.name_product AS VARBINARY(MAX)) AS VARBINARY(MAX))
    FROM @tab t INNER JOIN CTE C
    ON t.id_parent = C.id
)

select * from cte order by SORTKEY

спасибо

Вариант на основе @onaiggac (вы можете использовать его данные :-))

;WITH CTE (id, id_parent, name_product, LEVEL, SORTKEY) AS
(
    SELECT id, id_parent, name_product, 1, 
        CAST(ROW_NUMBER() OVER (ORDER BY name_product) AS VARBINARY(MAX))
    FROM @tab where id_parent is null -- Starts with the first level

    UNION ALL

    SELECT t.id, t.id_parent, t.name_product, C.LEVEL + 1, 
        C.SORTKEY + CAST(ROW_NUMBER() OVER (ORDER BY t.name_product) AS VARBINARY(MAX))
    FROM @tab t 
    INNER JOIN CTE C ON t.id_parent = C.id
)

SELECT id, id_parent, REPLICATE('   ', LEVEL - 1) + name_product, LEVEL, SORTKEY FROM CTE ORDER BY SORTKEY

Хитрость здесь заключается в использовании

ROW_NUMBER() OVER (ORDER BY name_product)

сделать «внутренний» заказ. Как и в @onaiggac, это затем составляется в двоичном формате varbinary(max)

CAST(ROW_NUMBER() OVER (ORDER BY name_product) AS VARBINARY(MAX))

который затем рекурсивно добавляется...

C.SORTKEY + CAST(ROW_NUMBER() OVER (ORDER BY t.name_product) AS VARBINARY(MAX))

Обратите внимание, что ROW_NUMBER() вернет bigint... вы можете привести его к int перед преобразованием к VARBINARY(MAX), например

CAST(CAST(ROW_NUMBER() OVER (ORDER BY name_product) AS INT) AS VARBINARY(MAX))

если вы действительно хотите... Я не думаю, что это необходимо, если только ваше дерево не действительно глубокое.