good one - no doubt about it. I hadn't yet seen the merry-go-round in action, but it does make sense now.

From what I've seem, the OVER syntax, and the ranking functions give you no advantage whatsoever on anything except for counts in this particular case. It's in my mind one of the failings/shortcoming of that syntax, since with all it does, you STILL have to do triangle joins or this kind of technique to get any kind of running aggregate other than a count (which you get implicitly through the ROW_NUMBER() function). I am pretty sure that's part of the OVER syntax from Ansi that didn't make the 2005 implementation (as I recall, that OTHER database product does running aggregates using its OVER syntax)..

And - just like you'd expect - using a CTE to try to force the order does nothing for you. It operates the same as a view does (inner ORDER BY is ignored).