0

Could you please help me with this dillema?

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: recursive cte with ranking functions ). 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?

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.

I'm using SQL Server 2012.

Thanks!

--this works, however it's not recursive and I don't know in advance how many "levels" there will be:

;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 > 0

--this doesn't work:

;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 > 0
)
select *
from r

--this also fails:

;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 > 0
)
select *
from r
Community
  • 1
  • 1
Vlad
  • 1
  • 1
  • Perhaps it's because you simplified too much but this looks like you're trying implement a running total without using Window Frames (which in 2012 you shouldn't do) – Conrad Frix Jun 24 '16 at 16:17
  • @ConradFrix The actual code is part of a matrix inverse calculation. The SQL that I'm trying to write will need to update each value from each line and column of the matrix using a complicated formula which pretty much uses all the values calculated in the previous "step". What would you suggest I would use instead, if Window Frames is something I shouldn't do? – Vlad Jun 26 '16 at 14:45
  • I'm suggesting *using* Windows Frames (e.g. `ROWS UNBOUNDED PRECEDING`) instead of using recursive CTEs. This is something that wasn't available in SQL Server until 2012. – Conrad Frix Jun 27 '16 at 14:09

0 Answers0