CODEWITHSUNDEEP
sqlsql-servert-sqlcommon-table-expression
renakre
renakre

Reputation: 8291

CTE Recursion to get a certain number of descendants per parent

I have the following query to return the Parent items and the (only first 2) descendants along with the child count. However, the query only returns more than 2 descendants (Here is the SQL Fiddle: http://sqlfiddle.com/#!6/6489b/5).

;WITH cte AS (
SELECT  [EntryId],
        [Title],
        [ParentId],         
        [Depth],
        ROW_NUMBER() OVER (ORDER BY Date DESC) [Rn],
        CAST(EntryId AS VARCHAR(MAX)) [Path],
        1 AS DescendentCount 
FROM    ViewEntryRecords
WHERE   [Depth] = 0 AND DiscussionWallId = @DiscussionWallId
UNION ALL
SELECT  e.[EntryId],
        e.[Title],
        e.[ParentId],
        e.[Depth],
        Rn,
        [Path] + ',' + CAST(e.EntryId AS VARCHAR(MAX)),
        P.DescendentCount + 1
FROM    ViewEntryRecords S
        JOIN cte P ON P.EntryId = S.ParentId 
        WHERE S.Depth=0 AND P.DescendentCount <= 2
)

SELECT  [EntryId],
    [Title],
    [ParentId],
    [Depth],
    ChildCount,
    DescendentCount
FROM cte c1
    OUTER APPLY (SELECT COUNT (*) - 1 AS ChildCount 
                 FROM cte c2 
                 WHERE c2.[Path] LIKE c1.[Path] + '%'
                ) oa
WHERE   Rn BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
ORDER BY c1.[ParentId],  Date DESC

Here is the output. I included DescendentCount for testing:

 EntryId    Title   ParentId    Depth   ChildCount  DescendentCount
 15         a       NULL        0       5           1
 4          d       NULL        0       1           1
 1          c       NULL        0       7           1
 1          s       NULL        0       7           1
 22         a1      4           1       0           2
 24         da      15          1       0           2
 23         32      15          1       0           2
 16         b       15          1       2           2
 21         n       16          1       0           3
 20         y       16          1       0           3

So, the entry with ID: 16 should be listed since I used P.DescendentCount <= 2 where condition. However, I see that DescendentCount not incremented the way I thought. Is there a way to limit the descendent items?

Upvotes: 1

Views: 138

Answers (1)

RU Ahmed
RU Ahmed

Reputation: 558

You have to RANK the DescendentCount again and make it<=2(as you want only first two Descendent) in WHERE cause. I have modified your query that you gave in SQL Fiddle and ran it in my SqlServer Management Studio. Following TSQL is Perfectly Working :

;WITH cte AS (
SELECT  [EntryId],
        [Title],
        [ParentId],         
        [Depth],
        ROW_NUMBER() OVER (ORDER BY EntryId DESC) [Rn],
        CAST(EntryId AS VARCHAR(MAX)) [Path],
        1 AS DescendentCount 
FROM    Entries
WHERE   [Depth] = 0 
UNION ALL
SELECT  e.[EntryId],
        e.[Title],
        e.[ParentId],
        e.[Depth],
        Rn,
        [Path] + ',' + CAST(e.EntryId AS VARCHAR(MAX)),
        c.DescendentCount + 1
FROM    Entries e
        JOIN cte c ON c.EntryId = e.ParentId 
        WHERE e.Depth=1 AND c.DescendentCount <= 2
)

SELECT Raihan.[EntryId]
    ,Raihan.[Title]
    ,Raihan.[ParentId]
    ,Raihan.[Depth]
    ,Raihan.ChildCount
    ,Raihan.DescendentCount
    --,Raihan.NewRN
    FROM
(
SELECT  [EntryId],
    [Title],
    [ParentId],
    [Depth],
    ChildCount,
    DescendentCount,
    ROW_NUMBER() OVER (PARTITION BY DescendentCount ORDER BY DescendentCount DESC) AS NewRN
FROM  cte c1
    OUTER APPLY (SELECT COUNT (*) - 1 AS ChildCount 
                 FROM cte c2 
                 WHERE c2.[Path] LIKE c1.[Path] + '%'
                ) oa
) AS Raihan
WHERE Raihan.NewRN<=2 
ORDER BY Raihan.[ParentId]

Upvotes: 1

Related Questions