Why are CTE's unable to use grouping and other clauses?

Question

I recently learned about Recursive Common Table Expressions (CTE's) while looking for a way to build a certain view of some data. After taking a while to write out how the first iteration of my query would work, I turned it into a CTE to watch the whole thing play out. I was surprised to see that grouping didn't work, so I just replaced it with a "Select TOP 1, ORDER BY" equivalent. I was again surprised that "TOP" wasn't allowed, and came to find that all of these clauses aren't allowed in the recursive part of a CTE:

• DISTINCT
• GROUP BY
• HAVING
• TOP
• LEFT
• RIGHT
• OUTER JOIN

So I suppose I have 2 questions:

1. In order to better understand my situation and SQL, why aren't these clauses allowed?
2. If I need to do some sort of recursion using some of these clauses, is my only alternative to write a recursive stored procedure?

Thanks.

Answer 1

Referring to :-

1. In order to better understand my situation and SQL, why aren't these clauses allowed?

Based on my understanding of CTE's, the whole idea behind creating a CTE is so that you can create a temporary result-set and use it in a named manner like a regular table in SELECT, INSERT, UPDATE, or DELETE statements.

Because a CTE is logically very much like a view, the SELECT statement in your CTE query must follow the same requirements as those used for creating a view. Refer CTE_query_definition section in following link on MSDN

Also, because a CTE is basically a named resultset and it can be used like any table in SELECT, INSERT, UPDATE, or DELETE statements, you always have the option of using the various operators you mentioned when you use the CTE in any of those statements.

With regards to

2.If I need to do some sort of recursion using some of these clauses, 
is my only alternative to write a recursive stored procedure?

Also, i am pretty sure that you can use atleast some of the keywords that you have mentioned above in a view / CTE select statement. For Eg: Refer the use of Group By in a CTE here in the Creating a simple common table expression example

Maybe if you can provide a sample scenario of what you are trying to achieve, maybe we can suggest some possible solution