I agree. I must confess I'd never heard of them before, but I'm pretty new to SQL development and admin.

In this case, the SQL statement needed to run with a VB6 based application and not through SQL Management Studio. The application didn't support the CTE so I had to use the sub select in the end.