Reset Row Number in Oracle conditionally

Question

I have this record set returned , now I want to have a row number column which gets reset after every 3rd row. Can anyone help me with this? needs to be done with Oracle SQL. Explanation below-

data	current row number	rquired row number
Chris	1	1
Bryan	2	2
Jim	3	3
Davis	4	1
Kia	5	2
Jones	6	3
Mary	7	1
Carrie	8	2
Pearce	9	3
Cesar	10	1
Bob	11	2

Answer 1

You can mod the current value:

mod(current_row_num - 1, 3) + 1

So using a CTE to represent your current result set:

with your_result (data, current_row_num) as (
  select 'Chris', 1 from dual union all
  select 'Bryan', 2 from dual union all
  select 'Jim', 3 from dual union all
  select 'Davis', 4 from dual union all
  select 'Kia', 5 from dual union all
  select 'Jones', 6 from dual union all
  select 'Mary', 7 from dual union all
  select 'Carrie', 8 from dual union all
  select 'Pearce', 9 from dual union all
  select 'Cesar', 10 from dual union all
  select 'Bob', 11 from dual
)
select data, current_row_num, mod(current_row_num - 1, 3) + 1 as required_row_num
from your_result
order by current_row_num

DATA	CURRENT_ROW_NUM	REQUIRED_ROW_NUM
Chris	1	1
Bryan	2	2
Jim	3	3
Davis	4	1
Kia	5	2
Jones	6	3
Mary	7	1
Carrie	8	2
Pearce	9	3
Cesar	10	1
Bob	11	2

db<>fiddle