How to sort recursive queries in SQL Server?

Question

I wrote a query in this way, but I want to sort it in the way shown below. However, the comments are not sorted and you cannot get the desired result.

create table tree_table
(
    id int not null, 
    parent_id int not null, 
    name nvarchar(30) not null 
);

insert into tree_table (id, parent_id, name) 
values
(1, 0, '1Title'),
(2, 0, '2Title'),
(3, 0, '3Title'),
(4, 1,  '  ㄴRE 1Title 1-1'),
(5, 1,  '  ㄴRE 1Title 1-2'),
(6, 1,  '  ㄴRE 1Title 1-3'),
(7, 2,  '  ㄴRE 2Title 1-1'),
(8, 2,  '  ㄴRE 2Title 1-2'),
(9, 2,  '  ㄴRE 2Title 1-3'),
(10, 4, '    ㄴRE 1Title 1-1-1'),
(11, 4, '    ㄴRE 1Title 1-1-2'),
(12, 4, '    ㄴRE 1Title 1-1-3'),
(13, 3, '  ㄴRE 3Title 1-1'),
(14, 1, '  ㄴRE 1Title 1-4'),
(15, 6, '    ㄴRE 3Title 1-3-1'),
(16, 0, '4Title'),
(17, 16, '  ㄴRE 4Title 1-1'),
(18, 15, '      ㄴRE 3Title 1-3-1-1'),
(19, 16, '  ㄴRE 4Title 1-2'),
(20, 17, '    ㄴRE 4Title 1-1-1');

Select query:

WITH rcte AS
(
    SELECT 
        t.id AS 'thread',
        t.id,
        t.parent_id,
        t.name
    FROM
        tree_table t
    WHERE 
        t.parent_id = 0
    UNION ALL
    SELECT 
        r.thread,
        t.id,
        t.parent_id,
        t.name
    FROM
        tree_table t
    JOIN
        rcte r ON r.id = t.parent_id
),
sorted_threads AS
(
    SELECT 
        ROW_NUMBER() OVER (ORDER BY MAX(r.id) DESC) AS sort_number,
        r.thread
    FROM 
        rcte r
    GROUP BY 
        r.thread
)
SELECT
    st.sort_number,
    r.id,
    r.parent_id,
    r.name
FROM
    sorted_threads st
JOIN
    rcte r ON r.thread = st.thread
ORDER BY
    st.sort_number 

Result - sorted by the above query:

sort_number id    parent_id    name
----------- ----  ----------   ----------------
1           16    0            4Title
1           17    16             ㄴRE 4Title 1-1
1           19    16             ㄴRE 4Title 1-2
1           20    17               ㄴRE 4Title 1-1-1
2           1     0            1Title
2           4     1              ㄴRE 1Title 1-1
2           5     1              ㄴRE 1Title 1-2
2           6     1              ㄴRE 1Title 1-3
2           10    4                ㄴRE 1Title 1-1-1
2           11    4                ㄴRE 1Title 1-1-2
2           12    4                ㄴRE 1Title 1-1-3
2           14    1              ㄴRE 1Title 1-4
2           15    6                ㄴRE 3Title 1-3-1
2           18    15    　　　        ㄴRE 3Title 1-3-1-1
3           3     0            3Title
3           13    3              ㄴRE 3Title 1-1
4           2     0            2Title
4           7     2              ㄴRE 2Title 1-1
4           8     2              ㄴRE 2Title 1-2
4           9     2              ㄴRE 2Title 1-3

I want to sort like this - when comment, that post should go to the top:

sort_number id    parent_id    name
----------- ----  ----------   ----------------
1           16    0            4Title
1           17    16             ㄴRE 4Title 1-1
1           20    17               ㄴRE 4Title 1-1-1
1           19    16             ㄴRE 4Title 1-2
2           1     0            1Title
2           14    1              ㄴRE 1Title 1-4
2           15    6                ㄴRE 3Title 1-3-1
2           18    15    　　　        ㄴRE 3Title 1-3-1-1
2           6     1              ㄴRE 1Title 1-3
2           10    4                ㄴRE 1Title 1-1-1
2           11    4                ㄴRE 1Title 1-1-2
2           12    4                ㄴRE 1Title 1-1-3
2           5     1              ㄴRE 1Title 1-2
2           4     1              ㄴRE 1Title 1-1
3           3     0            3Title
3           13    3              ㄴRE 3Title 1-1
4           2     0            2Title
4           9     2              ㄴRE 2Title 1-3
4           8     2              ㄴRE 2Title 1-2
4           7     2              ㄴRE 2Title 1-1

How do I change the query to reflect like the above result?

Thanks for your answer

Answer 1

Build varchar sort key recursively and sort descending with a trick so that shorter values are considered greater.

WITH sorted AS
(
    SELECT 
        right(cast (1000000 + (select coalesce(max(t2.id),t.id) from tree_table t2 where t2.parent_id = t.id )as varchar(max)),6) AS [sort_key],
        t.id,
        t.parent_id,
        t.name
    FROM
        tree_table t
),
rcte AS (
    SELECT * 
    FROM  sorted t
    WHERE 
        t.parent_id = 0
    UNION ALL
    SELECT 
        r.sort_key + t.sort_key,
        t.id,
        t.parent_id,
        t.name
    FROM
        sorted t
    JOIN
        rcte r ON r.id = t.parent_id
)
SELECT * 
FROM rcte
ORDER BY 
   stuff(replicate(cast ('9' as varchar(max)), (select max(len(r2.sort_key)) from rcte r2)),1,len(sort_key), sort_key) 
DESC;

I assumed id <1000000 for simplicity, you can easily adjust it to any big value with bigint arithmetic.

Output :

sort_key                    id  parent_id   name
000019                      16  0   4Title
000019000020                17  16    ㄴRE 4Title 1-1
000019000020000020          20  17      ㄴRE 4Title 1-1-1
000019000019                19  16    ㄴRE 4Title 1-2
000014                      1   0   1Title
000014000015                6   1     ㄴRE 1Title 1-3
000014000015000018          15  6       ㄴRE 3Title 1-3-1
000014000015000018000018    18  15        ㄴRE 3Title 1-3-1-1
000014000014                14  1     ㄴRE 1Title 1-4
000014000012                4   1     ㄴRE 1Title 1-1
000014000012000012          12  4       ㄴRE 1Title 1-1-3
000014000012000011          11  4       ㄴRE 1Title 1-1-2
000014000012000010          10  4       ㄴRE 1Title 1-1-1
000014000005                5   1     ㄴRE 1Title 1-2
000013                      3   0   3Title
000013000013                13  3     ㄴRE 3Title 1-1
000009                      2   0   2Title
000009000009                9   2     ㄴRE 2Title 1-3
000009000008                8   2     ㄴRE 2Title 1-2
000009000007                7   2     ㄴRE 2Title 1-1