Database
Highlighted
Enthusiast

## Reset Moving Count

Greetings!

I am trying to create interval based on the Primary Key value so that on each interval I can select n number of rows.

Example:

Key values:

2

3

5

6

7

8

9

10

11

etc

So I want to count each sequence (cumalative) and for each 5 rows I want it to reset so the counts of 5 comes like this:

SEL count(*)  from table where Key between 2 and 7

So my quest is to get the number 2 and 7 and get the next two numbers so each time the count is always 5

Thanks,

Tags (2)

Accepted Solutions
Junior Contributor

## Re: Reset Moving Count

To get all rows assigned to groups of five:

```SELECT
(Row_Number()
Over (ORDER BY keycol) -1) / 5
FROM mytable```

To get one line per group of five:

```SELECT ...,
Over (ORDER BY keycol)
--  MIN(keycol)  -- pre-TD16.10
--   Over (ORDER BY keycol
--   rows between 4 following and 4 following)
FROM mytable
QUALIFY
Row_Number()
Over (ORDER BY keycol) MOD 5 = 1```

1 ACCEPTED SOLUTION
4 REPLIES 4
Junior Contributor

## Re: Reset Moving Count

Can you show the expected result?

Enthusiast

## Re: Reset Moving Count

Sorry, should have posted that earlier. Here is what I am trying :

COUNT(*)   , MIN_NUM , MAX_NUM

-----------------------------------------------

5                 2                           7

5                 8                           12 and so forth

Note, count(*) is always 5 and MIN Number is whatever the lowest number we start and next max number we pick such so that the between number's count is always 5.  Hope that clarifies it

Junior Contributor

## Re: Reset Moving Count

To get all rows assigned to groups of five:

```SELECT
(Row_Number()
Over (ORDER BY keycol) -1) / 5
FROM mytable```

To get one line per group of five:

```SELECT ...,
Over (ORDER BY keycol)
--  MIN(keycol)  -- pre-TD16.10
--   Over (ORDER BY keycol
--   rows between 4 following and 4 following)
FROM mytable
QUALIFY
Row_Number()
Over (ORDER BY keycol) MOD 5 = 1```

Enthusiast

## Re: Reset Moving Count

Awosome!

Learned something new. The 'Lead' did the trick.  Thank you for your help :)