[zasdffaa]

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.