Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Recursive Queries in SQL Server 2005 Expand / Collapse
Author
Message
Posted Friday, February 25, 2005 10:31 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 28, 2005 7:13 AM
Points: 18, Visits: 1
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sSampath/recursivequeriesinsqlserver2005.asp

HTH,
Srinivas Sampath
Blog: http://blogs.sqlxml.org/srinivassampath
Post #164151
Posted Thursday, March 3, 2005 1:34 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 24, 2013 1:55 AM
Points: 178, Visits: 465

Thanks Srinivas,

Very good description of recursive common table expressions and great sample for getting hierarchical data with a defined value of drill down levels (sample 1).

I liked the sample 1. I believe it will be very useful for many of us.

 

Eralper

http://www.kodyaz.com

 



Eralper
SQL Server and T-SQL Tutorials and Articles
Microsoft Certification and Certification Exams
Post #165307
Posted Wednesday, March 9, 2005 11:03 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Saturday, March 8, 2014 4:25 PM
Points: 1,415, Visits: 1,228

The sample VB.net code for comparison contains both syntax and logic errors.

In VB.Net, an IF is ended by "End If", not End.

Also, "If result = number * Factorial(number - 1)" is attempted assignment and comparison and produces an incorrect result. If you run this code, you always get zero as a result.

Refactored code:

Private Function Factorial(ByVal number As Integer) As Integer
  If number = 0 Then
    Return 1
  Else
    Return (number * Factorial(number - 1))
  End If
End Function

Post #166652
Posted Friday, March 17, 2006 12:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 25, 2009 6:00 AM
Points: 3, Visits: 13
Thanks for taking the time to post this article.

I'm curious as to performance compared to other methods of getting heirarchical data like adjacency, nested, etc.?
Post #266428
Posted Friday, March 17, 2006 7:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 29, 2008 11:44 AM
Points: 4, Visits: 5
the link to the previous article is wrong, it refers to the same article.
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=213&messageid=164149

I didn't know what CTE's were, glad I found out!!
This was the final straw, I'm upgrading from 2000.




Post #266541
Posted Wednesday, May 24, 2006 10:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 10, 2010 1:29 PM
Points: 2, Visits: 16
Thanks for this article. I have one question here - how can I sort the result from CTE in hierarchical order as well as siblings on some orther order say all records of one level by firstname. In the article the example only sorts it ib hierarchical order
Post #282525
Posted Sunday, March 18, 2007 11:39 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 5, 2014 3:14 AM
Points: 1, Visits: 55

Thanks for the article.

My questions is : Suppose we need to relate one row data to two parents. By doing that I get only one instance of that data, not both.

I've added : INSERT INTO dbo.SampleOrg SELECT 14, 'Senior Director - Finance', 5

It is not reasnable in this example but it is reasnable in other.

How it should be done to accomplish this need.

Post #352289
Posted Thursday, August 16, 2007 8:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 17, 2010 3:19 AM
Points: 3, Visits: 10

Hi,

 

One of the best example of recursive funtions in SQL. Thanks for posting such a nice example.

 

Raj

Post #391275
Posted Thursday, December 6, 2007 5:36 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 16, 2012 10:19 AM
Points: 46, Visits: 177
Nice explanation.

Assuming that a query is correctly structured, what in the data will cause extra rows in the query result. Does the recursion assume that each child has only one parent? What if this is not the case?

I have a table that has a "Father" field and "Child" field. Within the table a child can have more than one father. This is different from an org chart or an employee table where a child must have only one father. Is a recursive query possible when a child can have more than one father?

Thanks.
Post #430500
Posted Thursday, March 13, 2008 7:43 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 2:05 PM
Points: 36, Visits: 53
I would also like to know what performance gains if any you get when using CTE's.
Post #468697
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse