>As Naomi and Borislav said, you can't. The options are to redirect to a temp table, table variable, etc. if you need to use it multiple times. In that instance, it's questionable if a CTE is really necessary - you might not be gaining anything (unless you were using the CTE for recursion).

Well, the second CTE I execute is almost instantly as it was cached from the previous command. So, I am not sure if putting everything into a temporary table would make things faster.

>In case you're curious, the reason you can't is this....a CTE is really just syntax sugar - it allows you to take the syntax for a derived table subquery and place it "above" the outer query that uses it. If you actually try to run the CTE code itself without executing the subsequent line that uses it, you'll get an error.

Yes, thanks, I detected after after I posted the message.

>So the database engine is really just substituting the reference to the CTE in the main query with the SELECT statement in the CTE. Sort of like expanding a macro. So it's all gone after the line of code that uses the CTE.

Thanks