MS SQL 2005 Recursive Query Example

Lets take as an example following table:

cmsPage

PageId Parrent PageId

6 NULL

44 6

50 44

51 44

52 6

Lets find all children of page with PageId 44 using common table expressions:

WITH PagesList (PageId)

AS

(

    -- Anchor member definition

    SELECT PageId FROM cmsPage WHERE PageId=44

    UNION ALL

 

    -- Recursive member definition

    SELECT c.PageId From cmsPage as c

    INNER JOIN PagesList as p

    ON c.ParrentPageId = p.PageId

)

 

-- Statement that executes the CTE

SELECT PageId FROM PagesList

 

GO

 

Result would be:

44

50

51


Posted on Monday, March 10, 2008 by | Comments (3) | Add Comment

Comments

Gravatar

Re:MS SQL 2005 Recursive Queries Example

Thanks very much.. was very helpful

Posted on 10/6/2008 10:31:15 AM by Vamsi #
Gravatar

Re:MS SQL 2005 Recursive Queries Example

Nice query here. It helped me so much

Posted on 7/13/2009 9:26:24 AM by Undi #
Gravatar

Re:MS SQL 2005 Recursive Queries Example

Hi, Is there a way to sort this list? i.e. add a SortId column and then have each nested set of children ordered correctly etc... As I have found it very difficult to do this efficiently!

Posted on 8/19/2009 12:55:12 AM by Carl #
Gravatar

Hmm, I didn't get why is this difficult? You can modify last statement as follow:

SELECT PageId FROM PagesList ORDER BY PageId

New Comment

Your Name:
Email (for internal use only):
Comment:
 
Code above:

Categories

Recent Tweets

  • Simon Ince's Blog: Hierarchies with HierarchyID in SQL 2008 http://t.co/xSDwiF6rRS.
  • Visual Studio 2010 WAS painfully slow - CodeProject http://t.co/Usba1x6CZy

Valid HTML5