## Recursive Query

 Author Message sridharkannan7 SSC-Enthusiastic Group: General Forum Members Points: 128 Visits: 32 I've a table with data shown as belowDetailsID FollowupID1 02 03 14 25 3if 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 ChrisM@Work SSC Guru Group: General Forum Members Points: 96814 Visits: 20698 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 ShawFor 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 ModenExploring Recursive CTEs by Example Dwain Camps Geoff A SSCertifiable Group: General Forum Members Points: 6440 Visits: 1835 you can do this;`;WITHcteFindMax 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 Group: General Forum Members Points: 507481 Visits: 44277 sridharkannan7 (10/15/2010)I've a table with data shown as belowDetailsID FollowupID1 02 03 14 25 3if 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 childConsidering 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 ModenRBAR 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.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair Helpful Links:How to post code problemsHow to post performance problemsForum FAQs