I need a more reliable sort for CTE hierarchy query

Example table:

CREATE TABLE Fruit (
  ID int identity(1,1) NOT NULL,
  ParentID int NULL,
  Name varchar(255)
);

I want to sort parent and child records from the same table in alphabetical order (more than one level deep):

  • Select one column DISTINCT SQL
  • C# - Inserting multiple rows using a stored procedure
  • How to get DATE from DATETIME Column in SQL?
  • How to concatenate two strings in SQL Server 2005
  • Error: The conversion of a nvarchar data type to a smalldatetime data type resulted in an out-of-range value
  • How to find the size of data returned from a table
  • Apples
    --Green
    ----Just Sour
    ----Really Sour        
    --Red
    ----Big
    ----Small
    Bananas
    --etc.
    

    I attempted this:

    ;WITH CTE(ID, ParentID, Name, Sort) AS
    (
        SELECT 
             ID
            ,ParentID
            ,Name
            ,cast('\' + Name as nvarchar(255)) AS Sort          
        FROM Fruit
        WHERE ParentID IS NULL
    
        UNION ALL
    
        SELECT 
             a.ID
            ,a.ParentID
            ,a.Name
            ,cast(b.Sort + '\' + a.Name as nvarchar(255)) AS Sort           
        FROM Fruit a
        INNER JOIN CTE b ON a.ParentID = b.ID           
    )
    SELECT * FROM CTE Order by Sort
    

    This produces results for the sort like:

    \Apples
    \Apples\Green
    \Apples\Green\Just Sour
    \etc.
    

    Just when I thought things were good, it isn’t reliable. For example, if an item has more than one word. Like:

    \Apples
    \Apples A <-- culprit
    \Apples\Green
    

    If I can expand my question while I’m at it, I’d like to show actual hyphens or something in the results:

    Parent
    - Child
    --Grandchild
    

    The cruddy way I quickly did this was by adding a prefix column in the table with the value of - for all records. Then I could do this:

    ;WITH CTE(ID, ParentID, Name, Sort, Prefix) AS
    (
        SELECT 
             ID
            ,ParentID
            ,Name
            ,cast('\' + Name as nvarchar(255)) AS Sort  
            ,Prefix
    
        FROM Fruit
        WHERE ParentID IS NULL
    
        UNION ALL
    
        SELECT 
             a.ID
            ,a.ParentID
            ,a.Name
            ,cast(b.Sort + '\' + a.Name as nvarchar(255)) AS Sort
            ,cast(b.Prefix + a.Prefix as nvarchar(10)) AS Prefix
    
    
        FROM Fruit a
        INNER JOIN CTE b ON a.ParentID = b.ID           
    )
    SELECT * FROM CTE Order by Sort
    

    But that seems incorrect or not optimal.

    These hierarchical queries still give me a headache, so perhaps I’m just not seeing the obvious.

  • CTE with recursion - row_number() aggregated
  • What's your favored method for debugging MS SQL stored procedures?
  • Why is my using statement not closing connection?
  • How to reuse calculated columns avoiding duplicating the sql statement?
  • What's the most DRY-appropriate way to execute an SQL command?
  • Add apostrophe to last name search
  • 2 Solutions collect form web for “I need a more reliable sort for CTE hierarchy query”

    I tend to use row_number() ordered by Name in this case

    Example

    Declare @YourTable table (id int,ParentId  int,Name varchar(50))
    Insert into @YourTable values 
     ( 1, NULL,'Apples')
    ,( 2, 1   ,'Green')
    ,( 3, 2   ,'Just Sour')
    ,( 4, 2   ,'Really Sour')
    ,( 5, 1   ,'Red')
    ,( 6, 5   ,'Big')
    ,( 7, 5   ,'Small')
    ,( 8, NULL,'Bananas')
    
    Declare @Top    int         = null      --<<  Sets top of Hier Try 5 
    Declare @Nest   varchar(25) = '|-----'  --<<  Optional: Added for readability
    
    ;with cteP as (
          Select Seq  = cast(1000+Row_Number() over (Order by Name) as varchar(500))
                ,ID
                ,ParentId 
                ,Lvl=1
                ,Name 
          From   @YourTable 
          Where  IsNull(@Top,-1) = case when @Top is null then isnull(ParentId ,-1) else ID end
          Union  All
          Select Seq  = cast(concat(p.Seq,'.',1000+Row_Number() over (Order by r.Name)) as varchar(500))
                ,r.ID
                ,r.ParentId 
                ,p.Lvl+1
                ,r.Name 
          From   @YourTable r
          Join   cteP p on r.ParentId  = p.ID)
    Select A.ID
          ,A.ParentId 
          ,A.Lvl
          ,Name = Replicate(@Nest,A.Lvl-1) + A.Name
          ,Seq  --<< Can be removed
     From cteP A
     Order By Seq
    

    Returns

    ID  ParentId    Lvl Name                      Seq
    1   NULL        1   Apples                    1001
    2   1           2   |-----Green               1001.1001
    3   2           3   |-----|-----Just Sour     1001.1001.1001
    4   2           3   |-----|-----Really Sour   1001.1001.1002
    5   1           2   |-----Red                 1001.1002
    6   5           3   |-----|-----Big           1001.1002.1001
    7   5           3   |-----|-----Small         1001.1002.1002
    8   NULL        1   Bananas                   1002
    

    I’m going to guess you want this result

    \Apples
    \Apples\Green
    \Apples A
    

    Maybe try something like this:

    SELECT * 
    FROM CTE 
    ORDER BY replace(Sort, ' ', '~')
    

    '~' is ascii 126, You can also check using excel sorting.

    enter image description here

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.