>Can you clarify your requirements again and which DB you want them? I don't know what this recursive CTE was supposed to do.



Sql problem, how can i return record row no in table all row Thread #1535912 Message #1535912


He does not need a CTE imo

A simple select using row_number() should work http://msdn.microsoft.com/en-US/library/ms186734(v=SQL.90).aspx

>
>>yes , i use VFP ....
>>
>>if i change to 2005 SQL Server and use CTE.
>>it display error...
>>Msg 530, Level 16, State 1, Line 1
>>The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
>>
>>How can i do, thank you ~~~
>>

>>
>>WITH Tests(tid, tname, counts) AS
>>(
>> SELECT sysid, sysName, 0 AS counts
>> FROM syslib
>> UNION ALL
>> SELECT sysid, sysName, counts + 1
>> FROM syslib I
>> JOIN Tests T ON 
>> T.tid = I.sysid
>>)
>>SELECT sysid, sysName, T.counts
>> FROM syslib I
>> JOIN Tests T ON 
>> T.tid = I.sysid
>>
>>