I am trying (and failing) to correctly order my recursive CTE. My table consists of a parent-child structure where one task can relate to another on a variety of different levels.
For example I could create a task (this is the parent), then create a sub-task from this and then a sub-task from that sub-task and so forth..
Below is some test data that I have included. Currently it's ordered by Path
which orders it alphabetically.
So if I were to create a task. It would give me a TaskID for that task (Say 50) - I could then create 5 subtasks for that main task (51,52,53,54,55). I could then add sub-tasks to the 5 subtasks (51->56) (53->57) but when I want the order I would need it returning
So the order I would require it
- 50
- 51
- 56
- 52
- 53
- 57
- 54
- 55
- 51
Proper order of the test data
Here is the code that I have been using
DECLARE @TaskID NUMERIC(10,0)
SET @TaskID = 38
;WITH cte AS
(
SELECT
t.TaskID
,t.ParentID
,t.Title
,CONVERT(VARCHAR(MAX),'') AS [Nest]
,CONVERT(VARCHAR(MAX),'') AS [Path]
,t.CreatedDate
FROM
tasks.Tasks t
WHERE
t.ParentID IS NULL
AND t.TaskID = @TaskID
UNION ALL
SELECT
sub.TaskID
,sub.ParentID
,sub.Title
,cte.[Nest] + CONVERT(VARCHAR(MAX),sub.TaskID) AS [Nest]
,cte.[Path] + ',' + CONVERT(VARCHAR(MAX),sub.TaskID) AS [Path]
,sub.CreatedDate
FROM
tasks.Tasks sub
INNER JOIN cte ON cte.TaskID = sub.ParentID
)
SELECT
TaskID
,ParentID
,Title
,Nest
,[Path]
,CreatedDate
FROM (
SELECT
cte.TaskID
,cte.ParentID
,cte.Title
,NULLIF(LEN(cte.[Path]) - LEN(REPLACE(cte.[Path], ',', '')),0) Nest
,CONVERT(VARCHAR(25),@TaskID) + cte.[Path] AS [Path]
,cte.CreatedDate
FROM
cte
)a
ORDER BY
a.[Path]
I have a feeling it will be blindingly obvious but I'm really not sure how to proceed. I thought about more recursion, functions, splitting the string with no success.
Apologies if I'm not being clear
The easiest way would be to pad the keys to a fixed length. e.g.
038,007
will be ordered before038,012
But the padding length would have to be safe for the largest taskid. Although you could keep yourpath
trimmed for readability and create an extra padded field for sorting.A somewhat safer version would be to do the same, but create a padded path from row_numbers. Where the padding size would have to be big enough to support the maximum number of sub items.
You could probably go more fancy than a fixed subitem length, determining the amount of subitems and basing the padding on said length. Or using numbered rows based on the amount of siblings and traverse in reverse direction and maybe (just spouting some untested thoughts), but using a simple ordered path is likely enough.