How to sort boards generated through recursive query

Question

This query is a reply type board structure. I have a hard time sorting recursive queries, so I would like to seek help. I've been asking questions from several developers, but one problem didn't solve it. The default sorting is done with the latest ID value.

If posts are listed in order, replying to previous posts will bring them to the top Even if you reply to an answer, that post should always be at the top.

My Query

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'),
(21, 9,  '    ㄴRE 2Title 1-3-1'),
(22, 3,  '      ㄴRE 3Title 1-2');


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;

Result

The result i want

Due to ID comment #21, 2Title should be placed under 3Title.

I am not enough because I am a beginner. Please give me your advice.

Thanks for answering in advance!!

user_6436191 · Answer

You can find the max value for each node, by walking down the tree for each node, and taking the maximum id you find.

For example:

with
n as (
  select id as root, * from tree_table
 union all
  select n.root, t.*
  from n
  join tree_table t on t.parent_id = n.id
),
place as (
  select root, max(id) as max_id from n group by root
),
t as (
  select t.*, p.max_id
  from tree_table t
  join place p on p.root = t.id
)
select * from t order by id;

Result:

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

See running example at SQL Fiddle.

Then, you can use your current strategy to sort, this time using max_id instead of id.

user_14267425 · Answer

You can (in your recursive CTE), keep each row's top parent_id. As the name implies, this is the id of the top-most parent.

Then in the final select, you add a sub-query to get the sort for that top_parent_id (e.g., the max_id for that top_parent_id) then use that for sorting all the values linked to that top_parent_id e.g.,

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 *, 
     id AS top_parent_Id    -- Added this
FROM  sorted t
WHERE 
    t.parent_id = 0
UNION ALL
SELECT 
    r.sort_key + t.sort_key,
    t.id,
    t.parent_id,
    t.name,
    r.Top_parent_Id  -- Added this
FROM
    sorted t
JOIN
    rcte r ON r.id = t.parent_id
)

SELECT rcte.*   -- Modified this to just get rcte values
FROM rcte
-- Added following line
INNER JOIN (SELECT top_parent_id, MAX(id) AS MaxId FROM rcte GROUP BY top_parent_id) AS Parent_Sort ON rcte.top_parent_Id = Parent_sort.top_parent_Id   
ORDER BY 
Parent_sort.MaxID DESC,  -- Added this
stuff(replicate(cast ('9' as varchar(max)), (select max(len(r2.sort_key)) from rcte 
r2)),1,len(sort_key), sort_key) 
DESC;

How to sort boards generated through recursive query

2 Answers