Hello,
- DB version 11.2.0.2.0
I have following table:
create table test_table
(
id number,
row_order number,
case_id number,
case_version number
);
insert into test_table
(id, row_order, case_id, case_version)
values
(1, 0, 10, 101);
insert into test_table
(id, row_order, case_id, case_version)
values
(2, 0, 10, 101);
insert into test_table
(id, row_order, case_id, case_version)
values
(3, 0, 10, 101);
insert into test_table
(id, row_order, case_id, case_version)
values
(4, 0, 10, 101);
insert into test_table
(id, row_order, case_id, case_version)
values
(5, 0, 10, 102);
insert into test_table
(id, row_order, case_id, case_version)
values
(6, 0, 10, 102);
insert into test_table
(id, row_order, case_id, case_version)
values
(7, 0, 10, 103);
insert into test_table
(id, row_order, case_id, case_version)
values
(8, 0, 10, 103);
insert into test_table
(id, row_order, case_id, case_version)
values
(9, 0, 10, 103);
insert into test_table
(id, row_order, case_id, case_version)
values
(10, 0, 20, 104);
insert into test_table
(id, row_order, case_id, case_version)
values
(11, 0, 20, 104);
insert into test_table
(id, row_order, case_id, case_version)
values
(12, 0, 20, 104);
insert into test_table
(id, row_order, case_id, case_version)
values
(13, 0, 30, 105);
insert into test_table
(id, row_order, case_id, case_version)
values
(14, 0, 30, 105);
insert into test_table
(id, row_order, case_id, case_version)
values
(15, 0, 30, 106);
insert into test_table
(id, row_order, case_id, case_version)
values
(16, 0, 30, 107);
insert into test_table
(id, row_order, case_id, case_version)
values
(17, 0, 40, 108);
insert into test_table
(id, row_order, case_id, case_version)
values
(18, 0, 40, 108);
I need to update row_order column. I need to number records ascending according to id and take into consideration grouping by case_id and case_version. I can describe it in wrong way. Fina result should be like below:
ID ROW_ORDER CASE_ID CASE_VERSION
---------- ---------- ---------- ------------
1 1 10 101
2 2 10 101
3 3 10 101
4 4 10 101
5 1 10 102
6 2 10 102
7 1 10 103
8 2 10 103
9 3 10 103
10 1 20 104
11 2 20 104
12 3 20 104
13 1 30 105
14 2 30 105
15 1 30 106
16 1 30 107
17 1 40 108
18 2 40 108
Could anyone suggest solution?
Thank you in advance.
Best Regards,
Lukasz