SQL select step

Question

I need to fetch a number of rows between two column values, using a defined step value. For example, if the table looks like this:

Id  Name
-----------------------
1   Maria Anders 
2   Christina Berglund
3   Francisco Chang
4   Roland Mendel
5   Diego Roel
6   Eduardo Saavedra
7   Helen Bennett
8   Philip Cramer

and First = 3, Last = 7, Step = 2, query should return:

Id  Name
-----------------------
3   Francisco Chang
5   Diego Roel
7   Helen Bennett

I was thinking of using a modulo to specify which columns should be returned, with something like:

SELECT *
FROM Table 
WHERE (i-3) % 2 = 0

This approach will result in SQL Server iterating through the entire table and calculating the expression for each item. Since I expect to have relatively large step values, I would like to know if there is a strategy which would avoid this (possible using an index to "skip" items).

Is there a better (read: faster) way of doing this? (I am using MS SQL Server 2008 R2)

Answer 1

DECLARE @start int = 3,  @step int = 2,  @stop int = 7;

;WITH cte AS
(
    SELECT @start AS ID
    UNION ALL
    SELECT ID + @step FROM cte WHERE ID + @step <= @stop
)

SELECT * 
FROM cte JOIN MyTable M ON cte.ID = MyTable.ID

Answer 2

select * from table
where (id >= @start) 
AND (id<=@end) 
AND ((id-@start)%@step) = 0

Test case:

declare @start int =3,
@end int = 7,
@step int =2
;with t(id)
as
(
    select 1
    union select 2
    union select 3
    union select 4
    union select 5
    union select 6
    union select 7
    union select 8
)
select * from t
where (id >= @start) AND (id<=@end) and ((id-@start)%@step) = 0

output:

3
5
7