recursive cte with ranking functions

Question

How to use ranking functions in recursive cte? Here's simple example showing how I'm trying to do:

with cte as (
  select 1 a, 1 b union all select 1, 2 union all select 2, 3 union all select 2, 4
)
, rcte (a, b, c, d) as (
  select a, b, cast(0 as int), 1 
  from cte
  union all
  select a, b, cast(ROW_NUMBER() over (partition by a order by b) as int), d+1
  from rcte
  where d < 2
)
select * 
from rcte
where d=2
order by a, b

Why there's no ranking? Show me my mistake pls

Answer 1

EDIT

When you read the CTE documentation regarding recursion, you will notice that it has some limits, such as not being able to use subqueries, group-by, top. These all involve multiple rows. From limited testing, and checking the execution plan, as well as testing this query

with cte as (
  select 1 a, 1 b union all select 1, 2 union all select 1, 3 union all select 2, 4
)
, rcte (a, b, c, d) as (
  select a, b, cast(0 as int), 1 
  from cte
  union all
  select r.a, cte.b, cast(ROW_NUMBER() over (order by r.b) as int), r.d+1
  from rcte r inner join cte on cte.a=r.a
  where r.d < 2
)
select * 
from rcte
where d=2
order by a, b

I can only conclude:

1. Row_Number() does work in a CTE, when other tables are joined to produce a multi-row result set
2. From the results of numbering, it is clear that CTEs are processed in a single row through all iterations, row-by-row instead of multirow-by-multirow, even though it appears to iterate all rows simultaneously. This would explain why any of the functions that apply to multi-row operations are not allowed for recursive CTE.

Although I came to this conclusion easily, someone obviously took a lot more time to explain it in excruciating detail only 17 months ago...

In other words, this is the nature of SQL Server's implementation of the recursive CTE, so windowing functions will not work the way you expect it to.

---

For the benefit of others, the output is:

a           b           c           d
----------- ----------- ----------- -----------
1           1           1           2
1           2           1           2
2           3           1           2
2           4           1           2

Whereas you are expecting c to contain 1,2,1,2 instead of 1,1,1,1. This certainly seems like it could be a bug, since there is no documentation to say that windowing functions should not work in the recursive part of a CTE.

Note: row_number() returns bigint, so you can cast just the anchor(c) as bigint.

Since each iteration increases d, you could perform the windowing outside.

with cte as (
  select 1 a, 1 b union all select 1, 2 union all select 2, 3 union all select 2, 4
)
, rcte (a, b, d) as (
  select a, b, 1 
  from cte
  union all
  select a, b, d+1
  from rcte
  where d < 2
)
select a,b, ROW_NUMBER() over (partition by a,d order by b) c,d
from rcte
--where d=2
order by d, a, b

---

EDIT - insight

While answering another question^link, I played some more with recursive CTE. If you run it without the final ORDER BY, you can see how SQL Server is approaching the recursion. It is interesting that it goes backwards in this case, then does a full depth-first recursion on each row.

Sample table

create table Testdata(SomeID int, OtherID int, Data varchar(max))
insert Testdata select 1, 9, '18,20,22,alpha,beta,gamma,delta'
insert Testdata select 2, 6, ''
insert Testdata select 3, 8, '11,12,.'
insert Testdata select 4, 7, '13,19,20,66,12,232,1232,12312,1312,abc,def'
insert Testdata select 5, 8, '17,19'

A recursive query

;with tmp(SomeID, OtherID, DataItem, Data) as (
select SomeID, OtherID, LEFT(Data, CHARINDEX(',',Data+',')-1),
    STUFF(Data, 1, CHARINDEX(',',Data+','), '')
from Testdata
union all
select SomeID, OtherID, LEFT(Data, CHARINDEX(',',Data+',')-1),
    STUFF(Data, 1, CHARINDEX(',',Data+','), '')
from tmp
where Data > ''
)
select SomeID, OtherID, DataItem, Data
from tmp
-- order by SomeID

The output shows the CTE anchor processed in iteration one, then for whatever reason each row in the anchor set is recursed to completion (depth-first) before processing other rows.

Yet it does have its strange uses, as this answer shows