InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 06:46:00 GMT20http://www.sqlservercentral.com/Forums/Topic1005053-392-1.aspx[quote][b]sridharkannan7 (10/15/2010)[/b][hr]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[/quote]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?Sun, 24 Oct 2010 19:50:36 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1005053-392-1.aspxUse a nested sets model, then look for leaf nodes. Your bad DDL will only lead to stinking bad DML.Fri, 15 Oct 2010 17:10:32 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1005053-392-1.aspxyou can do this;[code="sql"];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[/code]Fri, 15 Oct 2010 06:54:00 GMTGeoff Ahttp://www.sqlservercentral.com/Forums/Topic1005053-392-1.aspxPost 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.Fri, 15 Oct 2010 06:24:36 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1005053-392-1.aspxI'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 childFri, 15 Oct 2010 05:07:47 GMTsridharkannan7