my question: why this query produce 11 rows ? ( and not only 10 ).
; with cteRanges as
(
select 700 as StartRange
union all
select StartRange + 10
from cteRanges
where StartRange < 800
)
select StartRange, lead( StartRange) over( order by StartRange asc ) as EndRange
from cteRanges
order by StartRange desc
Maybe it's easier if we rephrase the question to:
Why is value 800 produced when I clearly specified, I want it to be less than 800 (where StartRange < 800)?
The answer: you evaluate on "previous value" before producing the "new value", and thus get values from 700 - 800 (which is 11).
It's examine:
Description Expression Output
1st value 700 700
2nd value where 700 < 800 700 + 10 = 710
3rd value where 710 < 800 710 + 10 = 720
4th value where 720 < 800 720 + 10 = 730
5th value where 730 < 800 730 + 10 = 740
6th value where 740 < 800 740 + 10 = 750
7th value where 750 < 800 750 + 10 = 760
8th value where 760 < 800 760 + 10 = 770
9th value where 770 < 800 770 + 10 = 780
10th value where 780 < 800 780 + 10 = 790
11th value where 790 < 800 790 + 10 = 800