Home > OS >  Reset Row Number in Oracle conditionally
Reset Row Number in Oracle conditionally

Time:07-28

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

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

  • Related