SQLTeam.com | Weblogs | Forums

CTE - create range values

sql2014

#1

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


#2

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