When writing a Common Table Expression, which is the proper order of the clauses? Assume the ORDER BY, GROUP BY, and SELECT clauses apply to the outer query, not the query used to define the CTE.
Answer: WITH, SELECT, GROUP BY, ORDER BY
Explanation: When creating a Common Table Expression, the WITH clause is first and precedes the SELECT clause of the query. GROUP BY, if needed, is before ORDER BY, in a SELECT query. Note that the CTE contains it’s own query inside parenthesis, all of which is a part of the WITH clause and there can be multiple queries for separate CTEs that are being built.
Note: The question of day is taken from SqlServerCentral.com. I want to compile selected QOD to share.