SORTING Hierarchical Queries in SQL Server 2005

Question

I have following issue: I have a table for maintaining the hierarchical data. I'd like to use CTE from SQL 2005.

WITH tree (id, parentid, code, name) AS
(
    SELECT id, ofs.ParentID, ofs.code, ofs.name
      FROM OrganizationFeatures ofs
     WHERE ofs.ParentID IS NULL

    UNION ALL

    SELECT ofs.id, ofs.ParentID, ofs.code, ofs.name
      FROM OrganizationFeatures ofs
      JOIN tree ON tree.ID = ofs.ParentID
)

select * from tree

But I'd like to sort by code and the result shold be as following:

1
1/1
1/1/1
1/1/2
1/2/1
1/2/2
2
4/1

etc. Any ideas?

Answer 1

To get the concatenated values, you need to do it in the with.

To sort, you need to add an order by in the last select.

WITH tree (id, parentid, code, name) AS
(
    SELECT id, ofs.ParentID, ofs.code, ofs.name
      FROM OrganizationFeatures ofs
     WHERE ofs.ParentID IS NULL

    UNION ALL

    SELECT ofs.id, ofs.ParentID, tree.code+'/'+ofs.code, ofs.name
      FROM OrganizationFeatures ofs
      JOIN tree ON tree.ID = ofs.ParentID
)

select * from tree order by code

Also, if code is not a varchar, you will have to convert the code columns in this bit of code (tree.code+'/'+ofs.code) for it to work.

Answer 2

Loki, I had the similar query but it didn't sort by name as I wanted but by the code - it was Friday and I was overloaded.

Anyway, running your query gave me an error, it's necessary to cast; I had to change it following way:

WITH tree (id, parentid, name, code) AS
(
    SELECT id, ofs.ParentID, ofs.name, CAST(ofs.name as varchar(255))
      FROM OrganizationFeatures ofs
     WHERE ofs.ParentID IS NULL

    UNION ALL

    SELECT ofs.id, ofs.ParentID, ofs.name, CAST(tree.code+'/'+ofs.name as varchar(255))
      FROM OrganizationFeatures ofs
      JOIN tree ON tree.ID = ofs.ParentID
)

select * from tree order by code

The problem is that it's necessary to cast to a varchar despite of the fact that name is varchar. It's quite possible that varchar(255) isn't enough with large trees.

So I made a version where the above mention problem isn't so big:

WITH tree (id, parentid, name, code) AS
(
    SELECT id, ofs.ParentID, ofs.name, 
           CAST(ROW_NUMBER() OVER (ORDER BY ofs.name ASC) as varchar(255)) 
      FROM OrganizationFeatures ofs
     WHERE ofs.ParentID IS NULL

    UNION ALL

    SELECT ofs.id, ofs.ParentID, ofs.name, 
           CAST(tree.code +'/' + CAST(ROW_NUMBER() OVER (ORDER BY ofs.name ASC) as varchar(255)) as varchar(255))
      FROM OrganizationFeatures ofs
      JOIN tree ON tree.ID = ofs.ParentID
)

select * from tree order by code

But I don't like such solution where is necessary to cast. Is there any better solution?