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
Ambassador

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
Ambassador

Re: Reset Moving Count

Can you show the expected result?

Highlighted
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!

 

Ambassador

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

 

 

Enthusiast

Re: Reset Moving Count

Awosome!

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