Oracle SQL row counter with restarting incremental

Question

I would like to create a row counter with Oracle SQL. This row counter should start from 1 till 5 and then should restart again from 1.

Like:

ORDER, ROW_COUNTER
-----   -----------
ORDER1,1
ORDER2,2
ORDER3,3
ORDER4,4
ORDER5,5
ORDER6,1
ORDER7,2
ORDER8,3
ORDER9,4
ORDER10,5
ORDER11,1
ect...

Do you have any idea?

Do you have a column that specifies the ordering of the rows in the table? — Gordon Linoff, Sep 08 '20 at 14:13

score 1 · Answer 1 · answered Sep 08 '20 at 14:13

Use row_number() and arithmetics:

select 
    order_no, 
    1 + mod(row_number() over(order by order_no) - 1, 5) row_counter
from mytable

This assumes that the first column of the table can be used to order the records, although it would probably make more sense to have something more sensible than a string column (like an autoincremented column for example).

Side note: order is a SQL language keyword (as in: order by), hence not a good choice for a column name. I renamed it to order_no in the query.

Thanks! Yes, i used a dummy column name. But this is what i exactly wanted! — Andras Zsolnai, Sep 08 '20 at 15:15

score 1 · Answer 2 · answered Sep 08 '20 at 14:14

SQL tables represent unordered sets. There is no ordering, unless a column specifies the ordering.

But the basic idea is to use row_number() or rownum and some arithmetic:

select t.*,
       1 + mod(rownum - 1, 5)
from t;

Note: The results from this are not stable; the table can be read in any order.

Oracle SQL row counter with restarting incremental

2 Answers2