Megha P (3/22/2013)
Hello ,you can also try below
;WITH CTE AS
(
SELECT docid,pages FROM #x
UNION ALL
SELECT C.docid,C.pages-1
FROM #x X INNER JOIN CTE C
ON X.docid = C.docid
AND C.pages-1 >0
)
SELECT * FROM CTE ORDER BY docid,Pages
I'll throw in with the others on that. That's what is know as a "Counting rCTE". Another name for it is "Hidden RBAR". Please see the following article for a comparison of rCTEs to 3 other common methods. The rCTE loses on all fronts.
http://www.sqlservercentral.com/articles/T-SQL/74118/
--Jeff Moden
Change is inevitable... Change for the better is not.