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 |