You can only select, you can't nest cte, the query planner has no understanding of joins that cross cte boundaries so they are totally unoptimized, you can't use distinct or group by or etcetc. Really the CTE implementation in SqlServer is basically a parser level hack.
Show me any sql dialect that will allow that CTE you give here (edit; and what on earth is that supposed to actually mean)
> Or can you only use SELECT for WITH queries
you can only use a select inside a cte (or should be able to) because the 'e' stands for 'expression'. It seems postgres does allow an insert with and output which sort of makes sense but I doubt it's in the standard.
Your last sentence makes no sense to me. Give an example.
Also you failed to give an example that group by/distiinct weren't allowed in ctes.