How can I make index column start over after reaching 5th row? I can't do that with a window function as there are no groups, I just need an index with max number of 5 like this:

and so on.

Appreciate any ideas.

CodePudding user response：

You can use below solution for that purpose.

1. First, rank (row_number analytic function)the rows in your table within inline view
2. Then, use again the row_number function with partition by clause to group the previously ranked rows by TRUNC((rnb - 1)/5)

SELECT t."DATE"
   , row_number()over(PARTITION BY TRUNC((rnb - 1)/5) ORDER BY rnb) as "INDEX"
FROM (
  select "DATE", row_number()OVER(ORDER BY "DATE") rnb
  from Your_table
) t
ORDER BY 1
;

demo on db<>fiddle

CodePudding user response：

Your comment about using analytic functions is wrong; you can use analytic functions even when there are no "groups" (or "partitions"). Here you do need an analytic function, to order the rows (even if you don't need to partition them).

Here is a very simple solution, using just row_number(). Note the with clause, which is not part of the solution; I included it just for testing. In your real-life case, remove the with clause, and use your actual table and column names. The use of mod(... , 5) is pretty much obvious; it looks a little odd (subtracting 1, taking the modulus, then adding 1) because in Oracle we seem to count from 1 in all cases, instead of the much more natural counting from 0 common in other languages (like C).

Note that both date and index are reserved keywords, which shouldn't be used as column names. I used one common way to address that - I added an underscore at the end.

alter session set nls_date_format = 'dd.mm.rr';

with
  sample_inputs (date_) as (
    select date '2021-01-01' from dual union all
    select date '2021-01-02' from dual union all
    select date '2021-01-03' from dual union all
    select date '2021-01-04' from dual union all
    select date '2021-01-05' from dual union all
    select date '2021-01-06' from dual union all
    select date '2021-01-07' from dual
  )
select date_, 1   mod(row_number() over (order by date_) - 1, 5) as index_
from   sample_inputs
;

DATE_        INDEX_
-------- ----------
01.01.21          1
02.01.21          2
03.01.21          3
04.01.21          4
05.01.21          5
06.01.21          1
07.01.21          2

CodePudding user response：

You can combine MOD() with ROW_NUMBER() to get the index you want. For example:

select date, 1   mod(row_number() over(order by date) - 1, 5) as idx from t