Thanks for the encouragement Jeff! Frankly, this is why I've been trying to spend time on the forums lately...it seems almost every time I try to help someone, I learn something cool myself. 😀 The "cascading list sequencing" trick is something I hadn't encountered before, and it's great. I'm definitely adding that and ColdCoffee's "MAX() OVER" trick to my toolbox.
I've been playing with both solutions this morning trying to figure out which has the better performance. It actually looks like ColdCoffee's MAX() OVER version is more expensive than using the extra ROW_NUMBER(), at least from an I/O perspective; the execution plan shows a couple of extra Lazy Spools being aggregated and joined back to the final result set (I assume the result of the partitioned MAX() ) and this results in a significant amount of Worktable logical reads. I wonder if there's a way to get rid of that overhead, or if partitioning the MAX() function is inherently more expensive?
I have no idea at this point which is more effective from a CPU time perspective, though...I can't determine a clear winner with the small amount of test data.
Anyway, very cool stuff all the way around!