Order child items in a certain order in a multi-level hierarchy query in SQL Server 2012

Question

I have a table in SQL Server 2012 called Items which contains different items that are related to each other in a parent-child relationship through ParentId column. This table contains a topmost level item with ItemId of 429965 and its children, grandchildren, great grandchildren and so on i.e. it contains a multi-level hierarchy of a topmost level item.

A demo of my situation with sample data and query that I have tried is at SQL Fiddle of this problem. The calculated column of Position tells the order in which hierarchy items are shown.

I am trying to get the complete hierarchy of a topmost level item so that following requirements are met:

Parent is immediately followed by its child items in this multi-level hierarchy
Child items need to be shown in this hierarchy in an ascending order of CreateDate.

The query that I have tried achieves requirement #1, but not requirement #2.

Question

How can I achieve requirement #2 in addition to requirement #1 using existing recursive query? I cannot simply order by PathStr, CreateDate because no two paths are going to be the same in my multi-level hierarchy.

Schema and sample data creation queries

CREATE TABLE Items (
  ItemId int PRIMARY KEY,
  ParentId int,
  CreateDate datetime
);


INSERT Items
  VALUES (44129, 429965, CONVERT(datetime, '2016-01-01 17:30:55.760', 121)),
  (61291, 203905, CONVERT(datetime, '2016-01-02 20:18:35.770', 121)),
  (157898, 335625, CONVERT(datetime, '2016-01-01 00:00:06.420', 121)),
  (191951, 778472, CONVERT(datetime, '2016-01-01 00:00:01.400', 121)),
  (203905, 960767, CONVERT(datetime, '2016-01-01 00:00:01.310', 121)),
  (265468, 429965, CONVERT(datetime, '2016-05-01 06:07:26.690', 121)),
  (268246, 265468, CONVERT(datetime, '2016-10-06 13:41:55.990', 121)),
  (283015, 394157, CONVERT(datetime, '2017-12-03 01:58:08.710', 121)),
  (299356, 443367, CONVERT(datetime, '2016-01-01 00:00:01.400', 121)),
  (335625, 894441, CONVERT(datetime, '2016-11-06 21:27:00.270', 121)),
  (338413, 968392, CONVERT(datetime, '2016-11-21 07:15:48.010', 121)),
  (394157, 785375, CONVERT(datetime, '2016-05-19 09:19:28.500', 121)),
  (397189, 894441, CONVERT(datetime, '2016-01-01 13:34:03.980', 121)),
  (404536, 894441, CONVERT(datetime, '2016-01-01 00:00:16.850', 121)),
  (429965, 0, CONVERT(datetime, '2016-01-01 00:00:06.090', 121)),
  (439536, 968392, CONVERT(datetime, '2017-03-25 23:51:48.570', 121)),
  (443367, 191951, CONVERT(datetime, '2016-01-01 00:00:01.090', 121)),
  (778472, 394157, CONVERT(datetime, '2016-01-02 20:43:59.760', 121)),
  (785375, 910250, CONVERT(datetime, '2017-10-19 03:59:14.950', 121)),
  (894441, 265468, CONVERT(datetime, '2016-01-01 00:00:08.600', 121)),
  (910250, 268246, CONVERT(datetime, '2016-07-21 00:43:47.420', 121)),
  (927248, 785375, CONVERT(datetime, '2017-02-13 04:19:46.340', 121)),
  (960767, 335625, CONVERT(datetime, '2016-01-01 00:00:01.960', 121)),
  (968392, 785375, CONVERT(datetime, '2017-09-10 02:15:25.780', 121))

Query that I tried

WITH x (ItemId, ParentId, PathStr, CreateDate)
AS (SELECT
  ItemId,
  0 AS ParentId,
  CAST(ItemId AS varchar(max)) AS Pathstr,
  CreateDate
FROM Items
WHERE ItemId = 429965
UNION ALL
--get children for each parent ( c is for child table and x is for parent table)
SELECT
  i.ItemId,
  i.ParentId,
  x.PathStr + '-' + CAST(i.ItemId AS varchar(max)),
  i.CreateDate
FROM Items i
INNER JOIN x
  ON x.ItemId = i.ParentId)
SELECT
  *,
  ROW_NUMBER() OVER (ORDER BY PathStr) AS Position
FROM x;

In above query, items with Position of 2 and 24 appear in descending order of CreateDate and both are child items of same parent. If ordering was correct then item with position 24 should actually have had a position of 2, and item with position 2 should have had a position of 24.

Shnugo · Accepted Answer · 2018-01-29T10:00:56.760

you were close...

I added a Step column which you can use to see the Level in your hierarchy. Furthermore I added a DatePosition to check, whether all dates to a given Stel are in ascending order. Seems to be okay:

WITH x (ItemId, ParentId, PathStr, CreateDate,Step)
AS (SELECT
    ItemId,
    0 AS ParentId,
    CAST(ItemId AS varchar(max)) AS Pathstr,
    CreateDate,
    1 AS Step
FROM Items
WHERE ItemId = 429965
UNION ALL
--get children for each parent ( c is for child table and x is for parent table)
SELECT
    i.ItemId,
    i.ParentId,
    x.PathStr + '-' + CAST(i.ItemId AS varchar(max)),
    i.CreateDate,
    x.Step+1
FROM Items i
INNER JOIN x
    ON x.ItemId = i.ParentId)
SELECT
    *,
    ROW_NUMBER() OVER (ORDER BY PathStr) AS Position,
    ROW_NUMBER() OVER (ORDER BY CreateDate) AS DatePosition

FROM x
ORDER BY Step,DatePosition;

UPDATE: Threaded discussion...

Try this

WITH x (ItemId, ParentId, PathStr,CreateDate,Step)
AS (SELECT  ItemId,
            0 AS ParentId,
            CAST(REPLACE(STR(ItemId,9),' ','0') AS VARCHAR(MAX)) AS PathStr,
            CreateDate,
            1 AS Step
    FROM Items
    WHERE ItemId = 429965

    UNION ALL
    --get children for each parent ( i is for child table and x is for parent table)
    SELECT  i.ItemId,
            i.ParentId,
            CAST(x.PathStr + '-' + REPLACE(STR(i.SortNmbr,3),' ','0') AS VARCHAR(MAX)) + '-' + CAST(REPLACE(STR(i.ItemId,9),' ','0') AS VARCHAR(MAX)),
            i.CreateDate,
            x.Step+1
    FROM x
    CROSS APPLY(SELECT *, ROW_NUMBER() OVER(ORDER BY CreateDate) AS SortNmbr FROM Items WHERE Items.ParentId=x.ItemId) AS i
   )
SELECT *
FROM x
ORDER BY PathStr;

If you want to order a recursive cte's result, you must include everything needed into your PathStr. Furthermore this sorting is done alphanumerically, so you have to add zeros to all numbers up to an equal width.

I used CROSS APPLY (instead of an INNER JOIN) to get the related rows sorted and include the derived SortNmbr (padded too!) into the sort string. Alternatively one might include the date itself, but this leads to rather huge strings with deeper nesting.

My padding allows 9 digits for the ItemId and 3 digits for the count of answers per parent, which is to much probably. Reduce this accordingly to reduce the size of the sort string. And you can remove the hyphens, they are only for better reading.

@Sunil: The *recursive CTE* is traversing the hierarchy *top-down*. Starting with Step=1 it finds all children (Step=2), all of the grandchildren will get Step=3. One thing which might mix you up: If your date ranges are overlapping, the grand children (and deeper) might get mixed. I did not get it completely, what you need here... — Shnugo, Jan 29 '18 at 09:10
There is one issue I am seeing. Parent and its children should appear next to each other, just like in a threaded discussion forums. When I ran your query, I am seeing ItemId 894441 and its children not appearing next to each other, but ItemId 268246 is appearing in between them — Sunil, Jan 29 '18 at 09:11
My situation is like a discussion threads scenario The items table is like a discussion threads table. And when they are shown, a thread is shown with its replies directly under it; also all replies must be ordered from oldest to newest replies. All top level threads which are replied to, must also be ordered from oldest to newest. — Sunil, Jan 29 '18 at 09:24
If I order by `PathStr` the final result set, then I get parent thread and child threads next to each other, but without the CreateDate ordering in the children. — Sunil, Jan 29 '18 at 09:44
I noticed that rows with ParentId of 335625 are not appearing next to each other, which means children of this parent are not appearing together. — Sunil, Jan 29 '18 at 10:16
The *threaded discussion* will place the *answers* to a parent listed below. The ID 335625 appears in one solid block between row 7 and row 11 with the given sample. Seems pretty much okay, doesn't it? — Shnugo, Jan 29 '18 at 10:20
Sorry, my mistake. You are correct. 335625 will appear in proper sequence. Thanks and I appreciate your excellent help. — Sunil, Jan 29 '18 at 10:25

Order child items in a certain order in a multi-level hierarchy query in SQL Server 2012

1 Answers1

UPDATE: Threaded discussion...