To get the list of paths without including any sub-paths, you could try the following:
1. Reverse the recursion - start with the records with no
NextID
and work backwards.
2. Find the longest path for each "leaf" node.
Eg:
WITH RecursivePaths As
(
SELECT
ID,
ID As Leaf,
CAST(ID AS VARCHAR(MAX)) AS Path
FROM
Data
WHERE
NextID Is Null
UNION ALL
SELECT
D.ID,
R.Leaf,
CAST(D.ID AS VARCHAR(MAX)) + ' --> ' + R.Path
FROM
RecursivePaths As R
INNER JOIN Data As D ON D.NextID = R.ID
),
SortedPaths As
(
SELECT
Leaf,
Path,
ROW_NUMBER() OVER (PARTITION BY Leaf ORDER BY Len(Path) DESC) As RN
FROM
RecursivePaths
)
SELECT
Path
FROM
SortedPaths
WHERE
RN = 1
;
Output:
3 --> 6 --> 9 --> 10
2
4 --> 7
1 --> 5 --> 8
db<>fiddle[
^]
NB: This doesn't match the output in your question, which includes
8
twice. I am assuming that's an error in your question. Otherwise, you need to explain why you want to include that specific node in both the
1 ⇒ 5 ⇒ 8
path and also on its own.