2

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 > 0 
AND   Num < (21)

Result

enter image description here

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:

enter image description here

2 Answers 2

5

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

3
  • Thanks. Another Problem is if i want to retrieve value that start from 4, 8, 12, etc. how can i do..
    – M.I.M.F
    Jun 22, 2017 at 22:06
  • Start From Val 4, after 8, 12, 16, 20.. (retrieve value not from the beginning such as 1)
    – M.I.M.F
    Jun 22, 2017 at 22:07
  • 1
    The change it: where (val) % 4 = 0
    – McNets
    Jun 22, 2017 at 22:08
2

Add another condition

AND Num % 4 = 1

Or simply list all the values:

WHERE Num IN (1,5,9,13,17,21,25)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.