title: SQL Server calculations based on aggregates inside recursive CTEs
body: <p>Could you please help me with this dillema?</p>
  
  <p>I tried to simplify the example as much as I could, but basically, what I want is to somehow use an aggregate of the previous results of a recursive query inside the next level of the recursive query (hope that makes sense).
  I tried using window functions (max() over()), however those seem to focus on only the current row for some reason (that seems better explained here: <a href="https://stackoverflow.com/questions/5416512/recursive-cte-with-ranking-functions">recursive cte with ranking functions</a> ).
  I also tried referencing the 'r' CTE more than once, however that seems illegal.
  Do you have any other ideas of how I can do this?</p>
  
  <p>I need to do this in SQL and not T-SQL. The reason is that I actually have a working version in T-SQL using loops, but the performance of that is pretty poor for what I'm trying to do. I'm hoping a pure SQL solution will work much faster.</p>
  
  <p>I'm using SQL Server 2012.</p>
  
  <p>Thanks!</p>
  
  <p>--this works, however it's not recursive and I don't know in advance how many "levels" there will be:</p>
  
  <pre><code>;with t as (
      select 1 a, 1 b union all
      select 2 a, 1 b union all
      select 3 a, 1 b
  ), r as (
      select a, b, 1 lvl
      from t
  )
  select *
  from r
      union all --we took the "union all" outside the CTE, which means it's not recursive anymore
      select a + max(a) over(partition by b) a, --this now works as expected and returns "a + 3" on all cases
          b, lvl-1
      from r
      where lvl &gt; 0
  </code></pre>
  
  <p>--this doesn't work:</p>
  
  <pre><code>;with t as (
      select 1 a, 1 b union all
      select 2 a, 1 b union all
      select 3 a, 1 b
  ), r as (
      select a, b, 1 lvl
      from t
      union all
      select a + max(a) over(partition by b) a, --this returns the "max" over only the current row instead of doing the partition from what I expect to be the "previous step"
          b, lvl-1
      from r
      where lvl &gt; 0
  )
  select *
  from r
  </code></pre>
  
  <p>--this also fails:</p>
  
  <pre><code>;with t as (
      select 1 a, 1 b union all
      select 2 a, 1 b union all
      select 3 a, 1 b
  ), r as (
      select a, b, 1 lvl
      from t
      union all
      select a + (select max(a) from r r2 where r2.b = r.b) a, --this returns the "max" over only the current row instead of doing the partition from what I expect to be the "previous step"
          b, lvl-1
      from r
      where lvl &gt; 0
  )
  select *
  from r
  </code></pre>