Parent child hierarchy with order by on name

Question

I want to create SQL Server query for the following. I have data like:

Id       Name       parentId
1         STU        0
2         XYZ        5
3         PQR        5
4         EFG        0
5         ABC        0
6         HIJ        1
7         DEF        1

Here is the query I am using:

SELECT *
FROM TABLE
ORDER BY CASE WHEN parentId = 0 THEN id ELSE parentId END ASC, id ASC

Output (parent and its children are sorted together):

Id       Name       parentId
1         STU        0
6         HIJ        1
7         DEF        1
4         EFG        0
5         ABC        0
2         XYZ        5
3         PQR        5

Now I am required to sort by name as well, first by parent's name and then all children, also sorted by name. The expected output is:

Id       Name       parentId
5         ABC        0
3         PQR        5
2         XYZ        5
4         EFG        0
1         STU        0
7         DEF        1
6         HIJ        1

Does anyone have solution for this? I need strict SQL Server query for this.

P.S. There is going to have only one level of hierarchy.

Answer 1

Almost every hierarchy question in SQL Server is solved with a recursive cte. You didn't mention if there can be more than one level of hierarchy, but since there is no way to prevent that other then writing an instead of trigger or a udf based check constraint, I am going on the assumption that there can be multi-level hierarchy rows.
The trick to this answer is the fact that sorting numbers alphabetically is different than sorting them numerically.
If you sorted 1, 11, 2, 13, 21, 3 in a numeric sort,
you will get 1, 2, 3, 11, 13, 21.
However, sorting the same numbers in alphabetical sort,
you will get 1, 11, 13, 2, 21, 3.

Now, enough talk, let's see some code!
First, create and populate sample table (Please save us this step in your future questions):

DECLARE @T AS TABLE
(
    Id int,
    [Name] char(3),
    parentId int
)
INSERT INTO @T (Id, [Name], parentId) VALUES
(1, 'STU', 0),
(2, 'XYZ', 5),
(3, 'PQR', 5),
(4, 'EFG', 0),
(5, 'ABC', 0),
(6, 'HIJ', 1),
(7, 'DEF', 1),
(8, 'AAA', 3),
(9, 'ZZZ', 3)

Note: I've added two more rows for grandchildren to check multi-level hierarchy.

The cte:

;WITH CTE AS
(
    SELECT  Id, 
            [Name], 
            ParentId, 
            -- Row_Number returns a bigint - max value have 19 digits
            CAST(ROW_NUMBER() OVER(ORDER BY [Name]) as varchar(19)) As Sort
    FROM @T 
    WHERE parentId = 0

    UNION ALL

    SELECT  T.Id, 
            T.[Name], 
            T.ParentId,
            CAST(Sort + CAST(ROW_NUMBER() OVER(ORDER BY T.[Name]) as varchar(19)) as varchar(19))
    FROM @T T
    JOIN CTE ON T.parentId = CTE.Id 
)

The query:

SELECT Id, [Name], ParentId
FROM CTE 
ORDER BY Sort -- alphabetic sort will order 11 before 2...

Results:

Id  Name    ParentId
5   ABC     0
3   PQR     5
8   AAA     3
9   ZZZ     3
2   XYZ     5
4   EFG     0
1   STU     0
7   DEF     1
6   HIJ     1