Recursive query - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Recursive query

Author

Topic

sridhar3004
Starting Member

34 Posts

Posted - 2010-10-15 : 01:23:31

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

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-10-15 : 04:28:46

I found this to be an interesting problem so I spent some time on it (Peso would probably have done it in milliseconds, I spent a bit more

). Please note that if you have several rows on the last leaf only one of them will be returned, if you want all nodes in the last leaf change the ROW_NUMBER() to RANK().

DECLARE @table table (
  DetailsID int,
  FollowupID int
  )
INSERT INTO @table
SELECT 1, 0 UNION ALL 
SELECT 2, 0 UNION ALL
SELECT 3, 1 UNION ALL 
SELECT 4, 2 UNION ALL
SELECT 5, 3;

SELECT * FROM @table;

WITH cte (DetailsID, FollowupID, Level, Path) AS (
  SELECT DetailsID, FollowupID, Level = 0,
    Path = CAST(DetailsID AS varchar(max))
  FROM @table
  WHERE FollowupID = 0
  
  UNION ALL
  
  SELECT a.DetailsID, a.FollowupID, Level = b.Level + 1, 
    Path = Path + '/' + CAST(a.DetailsID AS varchar(max))
  FROM @table a
    INNER JOIN cte b
      ON a.FollowupID = b.DetailsID
  )
SELECT DetailsID, FollowupID, Level, Path 
FROM cte 
WHERE Level = 0

UNION

SELECT DetailsID, FollowupID, Level, Path 
FROM  (
    SELECT *, RowNum = ROW_NUMBER() OVER (PARTITION BY LEFT(Path, PATINDEX('%/%', Path)-1) ORDER BY Level DESC)
    FROM cte
    WHERE Level > 0
    ) AS a
WHERE RowNum = 1
ORDER BY Path

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-10-15 : 07:24:53

"Last child" according to what?

;WITH cteSource
AS (
	SELECT	DetailsID AS GrandParentID,
		DetailsID AS ParentID,
		0 AS [Level]
	FROM	@Table
	WHERE	FollowupID = 0

	UNION ALL

	SELECT		s.GrandParentID,
			t.DetailsID AS ParentID,
			s.[Level] + 1 AS [Level]
	FROM		cteSource AS s
	INNER JOIN	@Table AS t ON t.FollowUpID = s.ParentID
)
SELECT	GrandParentID AS FollowUpID,
	ParentID AS DetailsID
FROM	(
		SELECT	GrandParentID,
			ParentID,
			RANK() OVER (PARTITION BY GrandParentID ORDER BY [Level] DESC) AS Rnk
		FROM	cteSource
	) AS d
WHERE	Rnk = 1

N 56°04'39.26"
E 12°55'05.63"

sridhar3004
Starting Member

34 Posts

Posted - 2010-10-15 : 07:41:48

Last Child of each parent. The output should return 2 columns, the parent column and the last child

So in the example I mentioned, it'll give

1 5
2 4

sridhar3004
Starting Member

34 Posts

Posted - 2010-10-15 : 07:43:58

Thank you. This is exactly what I was looking.

Thank you veyr much

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources