Recursive Query

Question

Post reply

Recursive Query

sridharkannan7

Mr or Mrs. 500

Points: 518
More actions
October 15, 2010 at 5:07 am

#229767

I've a table with data shown as below
DetailsID FollowupID
1 0
2 0
3 1
4 2
5 3
if you look at the above data, "3" is a followup of 1 and "5" is a followup of "3"
Similarly "4" is a followup of "2"
and 1 and 2 are original ids or parent ids.
I've to write a query that will retrieve only the parent and the last child of that parent.
I used recursion but that retrieves the entire chain. How do I further get only the parent and the last child

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Post what you've tried already.

Can you set up some sample data? One of the links in my sig will show you how to do this.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Geoff A SSChampion Points: 11511 More actions · Answer 2

you can do this;

;

WITH

cteFindMax AS

(

SELECT ROW_NUMBER() OVER (PARTITION BY DetailsID, FollowupID ORDER BY FollowupID DESC) AS Occurance,

*

FROM [the name of your table]

)

SELECT *

FROM cteFindMax

WHERE Occurance = 1

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 3

sridharkannan7 (10/15/2010)
I've a table with data shown as below
DetailsID FollowupID
1 0
2 0
3 1
4 2
5 3
if you look at the above data, "3" is a followup of 1 and "5" is a followup of "3"
Similarly "4" is a followup of "2"
and 1 and 2 are original ids or parent ids.
I've to write a query that will retrieve only the parent and the last child of that parent.
I used recursion but that retrieves the entire chain. How do I further get only the parent and the last child

Considering the LOS that some folks gave you on this, allow me to ask... did you ever get an answer for this that worked for you?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)