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-

CodePudding user response：

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

db<>fiddle