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.
___________________________________
Computer Enterprise Masoud Keshavarz
I don't care about hell.
If I go there I've played enough Diablo to know how to fight my way out.