Reset Moving Count

Database
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 ...,
   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
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

thanks in advance!

 

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 ...,
   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

 

 

Highlighted
Enthusiast

Re: Reset Moving Count

Awosome!

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