masoudk1990 (3/19/2013)

---

Thank you Horatiu, mickyT, Erin Ramsay for your time and help.

@SQLRNNR

May you help me to trace your code on paper please?

Im a newbie and im not sure how does it work.

In my real table ID is NOT sequential. Thats why I tried to generate row number using ROW_NUMBER() function.

First, I think you forget to write WHERE statement in anchor and you meant something like this:

WITH CTE (ID,Value) AS

(

SELECT ID,

Value

FROM @valuestab

WHERE ID = (SELECT MIN(ID) FROM @valuestab) --I guess you forget this line

UNION ALL

So your anchor should generate this output:

ID|Value

--------

1 | 19

Now we must union anchor with second part of query until it satisfied all rows.

At first run this condition b.ID +1 = a.ID change our table to this:

ID|Value

--------

1 | 19

2 | 19 + 90

Now it makes me confused. Once again we should check this condition b.ID +1 = a.ID

Does it check this condition only on last row or it check it on all rows?

If it check it on all rows it should generate this output:

ID|Value

--------

1 | 19

2 | 19 + 90

2 | 19 + 90 <-----surplus

3 | 19 + 90 + 20

I have problem with recursive statements, I know what is this, I know how we should write it.

But I still have logical problem with its mechanism.

Thank you for help.