Database
Highlighted

## 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

## 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 ...,
Lead(keycol,4)  -- TD 16.10+
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

## Re: Reset Moving Count

Can you show the expected result?

## 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

## 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 ...,
Lead(keycol,4)  -- TD 16.10+
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```

## Re: Reset Moving Count

Awosome!

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