title: Why are recursive CTEs unable to use grouping and other clauses?
body: <p>I recently learned about Recursive Common Table Expressions (CTEs) 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:</p>
  
  <ul>
  <li>DISTINCT</li>
  <li>GROUP BY</li>
  <li>HAVING</li>
  <li>TOP</li>
  <li>LEFT</li>
  <li>RIGHT</li>
  <li>OUTER JOIN</li>
  </ul>
  
  <p>So I suppose I have 2 questions:</p>
  
  <ol>
  <li>In order to better understand my situation and SQL, why aren't these clauses allowed?</li>
  <li>If I need to do some sort of recursion using some of these clauses, is my only alternative to write a recursive stored procedure?</li>
  </ol>
  
  <p>Thanks.</p>