Hi Tom,
Here is saved script:
https://livesql.oracle.com/apex/livesql/file/content_CEFPWL3UZTXJIA5QI852MTYXW.html I have this table
create table temp_mins (
barcode varchar2(20),
w_row varchar2(3),
w_col number(3,0),
keyid number(4,0),
comments varchar2(255)
);
with following records:
insert into temp_mins (barcode,w_row,w_col,keyid,comments) values ('AAAAAA-1','B',2,1,'xyzzzzz1');
insert into temp_mins (barcode,w_row,w_col,keyid,comments) values ('AAAAAA-1','B',3,1,'xyzzzzz2');
insert into temp_mins (barcode,w_row,w_col,keyid,comments) values ('AAAAAA-1','B',4,1,'xyzzzzz3');
insert into temp_mins (barcode,w_row,w_col,keyid,comments) values ('AAAAAA-1','C',2,2,'xyzzzzz11');
insert into temp_mins (barcode,w_row,w_col,keyid,comments) values ('AAAAAA-1','C',3,2,'xyzzzzz12');
insert into temp_mins (barcode,w_row,w_col,keyid,comments) values ('AAAAAA-1','C',4,2,'xyzzzzz13');
insert into temp_mins (barcode,w_row,w_col,keyid,comments) values ('AAAAAA-1','D',2,3,'xyzzzzz21');
insert into temp_mins (barcode,w_row,w_col,keyid,comments) values ('AAAAAA-1','D',3,3,'xyzzzzz22');
insert into temp_mins (barcode,w_row,w_col,keyid,comments) values ('AAAAAA-1','D',4,3,'xyzzzzz23');
insert into temp_mins (barcode,w_row,w_col,keyid,comments) values ('AAAAAA-1','E',2,4,'xyzzzzz31');
insert into temp_mins (barcode,w_row,w_col,keyid,comments) values ('AAAAAA-1','E',3,4,'xyzzzzz32');
insert into temp_mins (barcode,w_row,w_col,keyid,comments) values ('AAAAAA-1','E',4,4,'xyzzzzz33');
insert into temp_mins (barcode,w_row,w_col,keyid,comments) values ('AAAAAA-1','F',2,5,'xyzzzzz41');
insert into temp_mins (barcode,w_row,w_col,keyid,comments) values ('AAAAAA-1','F',3,5,'xyzzzzz42');
insert into temp_mins (barcode,w_row,w_col,keyid,comments) values ('AAAAAA-1','F',4,5,'xyzzzzz43');
insert into temp_mins (barcode,w_row,w_col,keyid,comments) values ('AAAAAA-1','G',2,6,'xyzzzzz51');
insert into temp_mins (barcode,w_row,w_col,keyid,comments) values ('AAAAAA-1','G',3,6,'xyzzzzz52');
insert into temp_mins (barcode,w_row,w_col,keyid,comments) values ('AAAAAA-1','G',4,6,'xyzzzzz53');
insert into temp_mins (barcode,w_row,w_col,keyid,comments) values ('AAAAAA-1','H',2,7,'xyzzzzz61');
insert into temp_mins (barcode,w_row,w_col,keyid,comments) values ('AAAAAA-1','H',3,7,'xyzzzzz62');
insert into temp_mins (barcode,w_row,w_col,keyid,comments) values ('AAAAAA-1','H',4,7,'xyzzzzz63');
insert into temp_mins (barcode,w_row,w_col,keyid,comments) values ('AAAAAA-1','A',2,4000,'xyzzzzz71');
insert into temp_mins (barcode,w_row,w_col,keyid,comments) values ('AAAAAA-1','A',3,4000,'xyzzzzz72');
insert into temp_mins (barcode,w_row,w_col,keyid,comments) values ('AAAAAA-1','A',4,4000,'xyzzzzz73');
insert into temp_mins (barcode,w_row,w_col,keyid,comments) values ('AAAAAA-2','D',4,5,'xyzzzzz81');
insert into temp_mins (barcode,w_row,w_col,keyid,comments) values ('AAAAAA-2','E',2,4,'xyzzzzz89');
insert into temp_mins (barcode,w_row,w_col,keyid,comments) values ('AAAAAA-2','E',3,4,'xyzzzzz90');
insert into temp_mins (barcode,w_row,w_col,keyid,comments) values ('AAAAAA-2','E',4,4,'xyzzzzz91');
insert into temp_mins (barcode,w_row,w_col,keyid,comments) values ('AAAAAA-2','F',2,3,'xyzzzzz99');
insert into temp_mins (barcode,w_row,w_col,keyid,comments) values ('AAAAAA-2','F',3,3,'xyzzzzz100');
insert into temp_mins (barcode,w_row,w_col,keyid,comments) values ('AAAAAA-2','F',4,3,'xyzzzzz101');
insert into temp_mins (barcode,w_row,w_col,keyid,comments) values ('AAAAAA-2','G',2,16,'xyzzzzz109');
insert into temp_mins (barcode,w_row,w_col,keyid,comments) values ('AAAAAA-2','G',3,16,'xyzzzzz110');
insert into temp_mins (barcode,w_row,w_col,keyid,comments) values ('AAAAAA-2','G',4,16,'xyzzzzz111');
insert into temp_mins (barcode,w_row,w_col,keyid,comments) values ('AAAAAA-2','H',2,7,'xyzzzzz119');
insert into temp_mins (barcode,w_row,w_col,keyid,comments) values ('AAAAAA-2','H',3,7,'xyzzzzz120');
insert into temp_mins (barcode,w_row,w_col,keyid,comments) values ('AAAAAA-2','H',4,7,'xyzzzzz121');
insert into temp_mins (barcode,w_row,w_col,keyid,comments) values ('AAAAAA-2','A',2,4000,'xyzzzzz129');
insert into temp_mins (barcode,w_row,w_col,keyid,comments) values ('AAAAAA-2','A',3,4000,'xyzzzzz130');
insert into temp_mins (barcode,w_row,w_col,keyid,comments) values ('AAAAAA-2','A',4,4000,'xyzzzzz131');
insert into temp_mins (barcode,w_row,w_col,keyid,comments) values ('AAAAAA-2','C',2,12,'xyzzzzz141');
insert into temp_mins (barcode,w_row,w_col,keyid,comments) values ('AAAAAA-2','C',3,12,'xyzzzzz142');
insert into temp_mins (barcode,w_row,w_col,keyid,comments) values ('AAAAAA-2','C',4,12,'xyzzzzz143');
insert into temp_mins (barcode,w_row,w_col,keyid,comments) values ('AAAAAA-2','D',2,5,'xyzzzzz151');
insert into temp_mins (barcode,w_row,w_col,keyid,comments) values ('AAAAAA-2','D',3,5,'xyzzzzz152');
insert into temp_mins (barcode,w_row,w_col,keyid,comments) values ('AAAAAA-2','B',2,11,'xyzzzzz153');
insert into temp_mins (barcode,w_row,w_col,keyid,comments) values ('AAAAAA-2','B',3,11,'xyzzzzz154');
insert into temp_mins (barcode,w_row,w_col,keyid,comments) values ('AAAAAA-2','B',4,11,'xyzzzzz155');
The output selects all columns + a new index column calculated as I am describing below.
For each keyid, it picks first set of (barcode, w_row,w_col) and assigns it an index.
BUT if (keyid, w_row, w_col) matches for any subsequent rows, it uses previous index for that keyid else assigns a new index.
examples rows:
barcode w_row w_col keyid comments
-------------------------------------------------------------------
AAAAAA-1 A 2 4000 xyzzzzz71 -> index :1
AAAAAA-2 A 2 4000 xyzzzzz129 -> index :1 (reuse the index because w_row, w_col is same as previously visited row for the keyid )
AAAAAA-1 E 2 4 xyzzzzz31 -> index : 5
AAAAAA-2 E 2 4 xyzzzzz89 -> index : 5 (reuse)
AAAAAA-1 H 2 7 xyzzzzz61 -> index : 8
AAAAAA-2 H 2 7 xyzzzzz119 -> index : 8 (reuse)
Final output should look like this:
barcode w_row w_col keyid comments TheIndex
-----------------------------------------------------------------
AAAAAA-1 A 2 4000 xyzzzzz71 1
AAAAAA-1 B 2 1 xyzzzzz1 2
AAAAAA-1 C 2 2 xyzzzzz11 3
AAAAAA-1 D 2 3 xyzzzzz21 4
AAAAAA-1 E 2 4 xyzzzzz31 5
AAAAAA-1 F 2 5 xyzzzzz41 6
AAAAAA-1 G 2 6 xyzzzzz51 7
AAAAAA-1 H 2 7 xyzzzzz61 8
AAAAAA-2 A 2 4000 xyzzzzz129 1
AAAAAA-2 B 2 11 xyzzzzz153 10
AAAAAA-2 C 2 12 xyzzzzz141 11
AAAAAA-2 D 2 5 xyzzzzz151 12
AAAAAA-2 E 2 4 xyzzzzz89 5
AAAAAA-2 F 2 3 xyzzzzz99 13
AAAAAA-2 G 2 16 xyzzzzz109 14
AAAAAA-2 H 2 7 xyzzzzz119 8
Appreciate any ideas.
Thanks