How to use T-SQL to select N rows every N rows?

I have a few hundred rows of data numbered with SELECT ROW_NUMBER() OVER (ORDER BY LastName).

I need to skip 3 of those rows every 3 rows in that selection. So I need to select rows 1, 2, 3… then skip rows 4, 5, 6… then include rows 7, 8, 9… then skip rows 10, 11, 12… and so on and so forth. I need to be able to do this dynamically, so that it will scale with any size selection.

(I’ll also need to get the inverse results of the above but presume I can extrapolate from the answer provided.)

Answer

You can use a subquery:

select t.*
from (select t.*, row_number() over (order by lastname) as seqnum
      from t
     ) t
where seqnum % 6 in (1, 2, 3);

Advertisement

Answer