CODEWITHSUNDEEP
t-sqlrecursionsql-order-bycommon-table-expression
Onaiggac
Onaiggac

Reputation: 561

Recursive CTE with order by

I'm working with a hierarchical data and using a recursive CTE to list the items like this:

Eletronics
    Televisions
        Tube
        LCD
        Plasma
    Portable Electronic
        MP3 Players
            Flash
        CD Player
        Two Way Radios

My question is:
How to do this list ordered by the title and respecting the hierarchy?

Like this:

Eletronics
    Portable Electronic
        CD Player
        MP3 Players
            Flash
        Two Way Radios
    Televisions
        LCD
        Plasma
        Tube

Tks

Upvotes: 4

Views: 3414

Answers (3)

xanatos
xanatos

Reputation: 111870

Variant based on @onaiggac (you can use his data :-) )

;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

The trick here is using 

ROW_NUMBER() OVER (ORDER BY name_product)

to do the "inner" ordering. As in @onaiggac, this is then composed in a binary varbinary(max)

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

that is then recursively additioned...

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

Note that ROW_NUMBER() will return a bigint... you could cast it to int before casting it to VARBINARY(MAX), like 

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

if you truly want... I don't think it is necessary unless your tree is really deep.

Upvotes: 1

Onaiggac
Onaiggac

Reputation: 561

Here is the correct CTE (only for asc)

;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

Upvotes: 4

Onaiggac
Onaiggac

Reputation: 561

Here is the code that I almost done what I want. The problem now is to order by desc

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

Tks

Upvotes: 1

Related Questions