How to Skip rows in a SQL Server Query? One I selected the query from my product table with an additional Row_Number() column (Num), I'm getting the answer like below (I limited only 20 rows to retrieve).

Query

SELECT * 
FROM (SELECT ROW_NUMBER() OVER(ORDER BY ProductCode) AS Num, 
             ProductName
      FROM   BASE_Product 
      WHERE IsActive = 1) BASE_Product 
WHERE Num &gt; 0 
AND   Num &lt; (21)


Result



My requirement is how to skip each and every 3 rows and retrieve only 7 limit rows.

Expected result

I need the result such as below:
You can use the Modulo operator for this purpose.


  Modulo
  Returns the remainder of one number divided by another.


In this case:

WHERE (Num - 1) % 4 = 0


I've set up a sample, generating a series of 1K integers.




--------------------------------------------------------------------------------
-- ② Integers table
--------------------------------------------------------------------------------
with
  p0(i) as (select 1 union all select 1 union all select 1 union all select 1)
, p1(i) as (select 1 from p0 as a, p0 as b, p0 as c, p0 as d, p0 as e)--1K rows
select row_number() over(order by i) as val
into integers
from p1;
GO



1024 rows affected






select top 25 * 
from integers
where (val - 1) % 4 = 0
GO



| val |
| :-- |
| 1   |
| 5   |
| 9   |
| 13  |
| 17  |
| 21  |
| 25  |
| 29  |
| 33  |
| 37  |
| 41  |
| 45  |
| 49  |
| 53  |
| 57  |
| 61  |
| 65  |
| 69  |
| 73  |
| 77  |
| 81  |
| 85  |
| 89  |
| 93  |
| 97  |



dbfiddle here