Manipulate each recursion group in recursive CTE

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Manipulate each recursion group in recursive CTE

Author

Topic

adit
Starting Member

8 Posts

Posted - 2012-03-02 : 05:17:34

Hi

I have built a recursive CTE on my table based on prev_id field. Need to get most recent ID(Active_ID) in a separate column for each recusion group. How can I do this? Below is the sample.

Recursion_Level Active_ID ID Prev_ID
0 4 1 NULL
1 4 2 1
2 4 3 2
3 4 4 3

Please share any ideas.

Thanks.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-02 : 07:02:24

Without seeing the details, two possibilities that I can think of:

1. Do the recursion in reverse, by finding the leaf nodes in the anchor part of the query and traversing up the tree in the recursive part. You can then carry along the leaf ID in the recursive part, which would be the active Id.

2. Use the results of the recursive query to find the active id (by finding the MAX(ID).

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-03-02 : 07:03:47

you could use row_number
Something like


; WITH cte AS (...)

SELECT
     *
FROM
    (
    SELECT
        ROW_NUMBER() OVER ( PARTITION BY [Active_ID] ORDER BY [ID] DESC ) AS [rnk]
        , cte.*
    FROM
        cte
    )
    AS rnked
WHERE
    rnked.[rnk] = 1

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources