Can you create nested WITH clauses for Common Table Expressions?

Question

WITH y AS (

    WITH x AS (

        SELECT * FROM MyTable

    )

    SELECT * FROM x

)

SELECT * FROM y

Does something like this work? I tried it earlier but I couldn't get it to work.

Answer 1

Try using common table expressions to reuse previous queries in subsequent ones. You need the form of the statement like this:

WITH x AS 

(

    SELECT * FROM MyTable

), 

y AS 

(

    SELECT * FROM x

)

SELECT * FROM y