Craig Farrell (10/20/2010)
Lutz, that is wild. I like it. Thanks. :w00t:Have you done that on huge recordsets? Does it perform well?
No, I haven't. And I probably won't either.
Such requirements and some rather large tables actually call for the quirky update from my point of view.
I don't expect the CTE to perform anywhere near the quirky update. Mainly because of the triple sort operation, the aggregation and the join on a range. But it should outperform any loop.
So, why did I posted this solution anyway?
#1: You already posted the link to Jeffs article.
#2: I did pretty much what you've already described. Just using T-SQL over English ;-):-D
#3: gmrose specifically asked for a cte solution and
#4: it was some kind of an exercise for me to do it using a non-quirky-update method.