Get all last members in a SQL Server hierarchy

Question

I am trying to find all first items of the last hierarchy members ("leafs") in a SQL Server hierarchy. I was able to find similar questions, however solutions with GetAncestors don't work with our SQL Server version.

I was able to use this solution:

SELECT 
    *, 
    CONVERT(nvarchar(20), MasterOrder) AS HierarchyString 
FROM 
    TestTable2 PARENT
WHERE
    NOT EXISTS (SELECT * 
                FROM TestTable2 CHILD
                WHERE CHILD.Id <> PARENT.Id 
                  AND CHILD.MasterOrder.IsDescendantOf(PARENT.MasterOrder) = 1)

This returns the correct results, but for some reason only 12 out of 25 rows. I think too many rows are included in the NOT EXISTS range, but I don't have experience with CHILD and PARENT keywords and interestingly enough, failed to find MS documentation for them.

EDIT: I couldn't find it, because I was very tired and somehow didn't noticed those are aliases, not keywords :-)

Here is data with marked rows (<<<), that the query should return.

CREATE TABLE TestTable2
(
     RowNo       INT NOT NULL, 
     ID          INT NOT NULL, 
     MasterOrder HierarchyID NOT NULL,
     SeqNo       INT NOT NULL 
)

INSERT INTO TestTable2 (RowNo, ID, MasterOrder, SeqNo) VALUES (1, 845192, '/', 1);
INSERT INTO TestTable2 (RowNo, ID, MasterOrder, SeqNo) VALUES (3, 845196, '/1/', 3);
INSERT INTO TestTable2 (RowNo, ID, MasterOrder, SeqNo) VALUES (2, 845194, '/1/', 2);
INSERT INTO TestTable2 (RowNo, ID, MasterOrder, SeqNo) VALUES (1, 845193, '/1/', 1);      -- <<<
INSERT INTO TestTable2 (RowNo, ID, MasterOrder, SeqNo) VALUES (2, 845238, '/10/', 2);
INSERT INTO TestTable2 (RowNo, ID, MasterOrder, SeqNo) VALUES (1, 845239, '/10/', 1);     -- <<<
INSERT INTO TestTable2 (RowNo, ID, MasterOrder, SeqNo) VALUES (2, 845242, '/11/', 2);
INSERT INTO TestTable2 (RowNo, ID, MasterOrder, SeqNo) VALUES (1, 845241, '/11/', 1);     -- <<<
INSERT INTO TestTable2 (RowNo, ID, MasterOrder, SeqNo) VALUES (3, 845245, '/12/', 3);
INSERT INTO TestTable2 (RowNo, ID, MasterOrder, SeqNo) VALUES (2, 845244, '/12/', 2);
INSERT INTO TestTable2 (RowNo, ID, MasterOrder, SeqNo) VALUES (1, 845243, '/12/', 1);     -- No! ...has children.
INSERT INTO TestTable2 (RowNo, ID, MasterOrder, SeqNo) VALUES (1, 845246, '/12/1/', 1);   -- <<<
INSERT INTO TestTable2 (RowNo, ID, MasterOrder, SeqNo) VALUES (2, 845264, '/12/10/', 2);
INSERT INTO TestTable2 (RowNo, ID, MasterOrder, SeqNo) VALUES (1, 845263, '/12/10/', 1);  -- <<<
INSERT INTO TestTable2 (RowNo, ID, MasterOrder, SeqNo) VALUES (1, 845247, '/12/2/', 1);   -- <<<
INSERT INTO TestTable2 (RowNo, ID, MasterOrder, SeqNo) VALUES (1, 845249, '/12/3/', 1);   -- <<<
INSERT INTO TestTable2 (RowNo, ID, MasterOrder, SeqNo) VALUES (1, 845251, '/12/4/', 1);   -- <<<
INSERT INTO TestTable2 (RowNo, ID, MasterOrder, SeqNo) VALUES (1, 845252, '/12/5/', 1);   -- <<<
INSERT INTO TestTable2 (RowNo, ID, MasterOrder, SeqNo) VALUES (1, 845254, '/12/6/', 1);   -- <<<
INSERT INTO TestTable2 (RowNo, ID, MasterOrder, SeqNo) VALUES (2, 845257, '/12/7/', 2);
INSERT INTO TestTable2 (RowNo, ID, MasterOrder, SeqNo) VALUES (1, 845256, '/12/7/', 1);   -- <<< 
INSERT INTO TestTable2 (RowNo, ID, MasterOrder, SeqNo) VALUES (2, 845260, '/12/8/', 2);
INSERT INTO TestTable2 (RowNo, ID, MasterOrder, SeqNo) VALUES (1, 845261, '/12/8/', 1);   -- <<<
INSERT INTO TestTable2 (RowNo, ID, MasterOrder, SeqNo) VALUES (1, 845262, '/12/9/', 1);   -- <<<
INSERT INTO TestTable2 (RowNo, ID, MasterOrder, SeqNo) VALUES (3, 845267, '/13/', 3);
INSERT INTO TestTable2 (RowNo, ID, MasterOrder, SeqNo) VALUES (2, 845268, '/13/', 2);
INSERT INTO TestTable2 (RowNo, ID, MasterOrder, SeqNo) VALUES (1, 845266, '/13/', 1);     -- <<<
INSERT INTO TestTable2 (RowNo, ID, MasterOrder, SeqNo) VALUES (3, 845272, '/14/', 3);
INSERT INTO TestTable2 (RowNo, ID, MasterOrder, SeqNo) VALUES (2, 845271, '/14/', 2);
INSERT INTO TestTable2 (RowNo, ID, MasterOrder, SeqNo) VALUES (1, 845269, '/14/', 1);     -- <<<
INSERT INTO TestTable2 (RowNo, ID, MasterOrder, SeqNo) VALUES (3, 845276, '/15/', 3);
INSERT INTO TestTable2 (RowNo, ID, MasterOrder, SeqNo) VALUES (2, 845275, '/15/', 2);
INSERT INTO TestTable2 (RowNo, ID, MasterOrder, SeqNo) VALUES (1, 845273, '/15/', 1);     -- <<<
INSERT INTO TestTable2 (RowNo, ID, MasterOrder, SeqNo) VALUES (2, 845278, '/16/', 2);
INSERT INTO TestTable2 (RowNo, ID, MasterOrder, SeqNo) VALUES (1, 845277, '/16/', 1);     -- No! ...has children.
INSERT INTO TestTable2 (RowNo, ID, MasterOrder, SeqNo) VALUES (1, 845279, '/16/1/', 1);   -- <<<
INSERT INTO TestTable2 (RowNo, ID, MasterOrder, SeqNo) VALUES (2, 845296, '/16/10/', 2);
INSERT INTO TestTable2 (RowNo, ID, MasterOrder, SeqNo) VALUES (1, 845297, '/16/10/', 1);  -- <<<
INSERT INTO TestTable2 (RowNo, ID, MasterOrder, SeqNo) VALUES (1, 845299, '/16/11/', 1);  -- <<<
INSERT INTO TestTable2 (RowNo, ID, MasterOrder, SeqNo) VALUES (2, 845301, '/16/12/', 2);
INSERT INTO TestTable2 (RowNo, ID, MasterOrder, SeqNo) VALUES (1, 845302, '/16/12/', 1);  -- <<<
INSERT INTO TestTable2 (RowNo, ID, MasterOrder, SeqNo) VALUES (1, 845304, '/16/13/', 1);  -- <<<
INSERT INTO TestTable2 (RowNo, ID, MasterOrder, SeqNo) VALUES (3, 845309, '/16/14/', 3);
INSERT INTO TestTable2 (RowNo, ID, MasterOrder, SeqNo) VALUES (2, 845308, '/16/14/', 2);
INSERT INTO TestTable2 (RowNo, ID, MasterOrder, SeqNo) VALUES (1, 845306, '/16/14/', 1);  -- <<<
INSERT INTO TestTable2 (RowNo, ID, MasterOrder, SeqNo) VALUES (3, 845313, '/16/15/', 3);
INSERT INTO TestTable2 (RowNo, ID, MasterOrder, SeqNo) VALUES (2, 845312, '/16/15/', 2);
INSERT INTO TestTable2 (RowNo, ID, MasterOrder, SeqNo) VALUES (1, 845310, '/16/15/', 1);  -- <<<
INSERT INTO TestTable2 (RowNo, ID, MasterOrder, SeqNo) VALUES (1, 845281, '/16/2/', 1);   -- <<<
INSERT INTO TestTable2 (RowNo, ID, MasterOrder, SeqNo) VALUES (1, 845282, '/16/3/', 1);   -- <<<

The RowNo column is originally a ROW_NUMBER column I added, it gives order number for the same hierarchy items (with identical hierarchy string). It has always one "1" for each leaf, even if the original sequence number starts i.e. with 3 (because 1 and 2 are excluded due to their state).

So the task is to get all the "leafs" with RowNo = 1.

Any help is appreciated.

score 1 · Answer 1 · answered Mar 03 '21 at 08:29

OK, analyzing it in detail I found the problem is apparently in the fact, that IsDescendantOf() is considering 3 different rows with same hierarchy value i.e. /13/ to be descendants of each other. My understanding of descendant was that it's a "child", especially because that is what documentation seems to state:

Returns true for all the nodes in the sub-tree rooted at parent, and false for all other nodes.

But as per my tests, it returns siblings too.

Once the problem was understood, it was easy to fix the code to get proper 25 rows:

SELECT 
    *, 
    CONVERT(nvarchar(20), MasterOrder) AS HierarchyString 
FROM 
    TestTable2 as PARENT
WHERE
    NOT EXISTS (SELECT * 
                FROM TestTable2 as CHILD
                WHERE RowNo = 1   -- <<< New condition <<<
                  AND CHILD.Id <> PARENT.Id 
                  AND CHILD.MasterOrder.IsDescendantOf(PARENT.MasterOrder) = 1)
                  ;

Adding RowNo = 1 into the selection of items to be excluded is ensuring that IsDescendantOf is performed only on those rows, so there are no siblings to play "false" childred.

Get all last members in a SQL Server hierarchy

1 Answers1