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:
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.
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;
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.