>>Kevin,
>>
>>Just an example how to do this in SQL 2005 without CTE's
>
>
>Wow, thanks, that actually performs better than anything I'd seen prior to the new windowing features in SQL 2012. My first inclination is to use CROSS APPLY though there's additional cost to that.
>
>
>On the CTEs - in absence of recursion, they offer no functional benefit - they are syntax sugar for derived table subqueries (though some people prefer sugar!).
>
>
>Thanks, great answers. Wrap-up tonight, have to get back to work (or my power-nap, whichever I decide) :)

I think CROSS APPLY and the Subquery shown by Walter are actually equivalent. I prefer CROSS APPLY as we can have more than 1 column.

Brad Schulz had a blog explaining that CROSS APPLY and subquery is the same.