Recursive Query

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Recursive Query

Recursive Query

Rate Topic

Display Mode

Topic Options

Author

Message

sridharkannan7

Posted Friday, October 15, 2010 5:07 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, July 21, 2011 4:15 AM
Points: 4, Visits: 21

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

Post #1005053

ChrisM@Work

Posted Friday, October 15, 2010 6:24 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 9:16 AM
Points: 5,693, Visits: 11,117

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
Exploring Recursive CTEs by Example Dwain Camps

Post #1005093

Geoff A

Posted Friday, October 15, 2010 6:54 AM

SSC-Addicted

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 10:25 AM
Points: 485, Visits: 1,569

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

Post #1005114

CELKO

Posted Friday, October 15, 2010 5:10 PM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782

Use a nested sets model, then look for leaf nodes. Your bad DDL will only lead to stinking bad DML.

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Post #1005638

Jeff Moden

Posted Sunday, October 24, 2010 7:50 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:56 PM
Points: 33,107, Visits: 27,026

sridharkannan7 (10/15/2010)

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1009763

« Prev Topic | Next Topic »

Permissions