Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, SP.

Asked: November 04, 2015 - 5:40 pm UTC

Last updated: November 06, 2015 - 2:18 am UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

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

and Connor said...

Something like this ? I omitted the 'comments' column

The "idx" column is the desired result (I think), the other two columns to assist with understanding

SQL> select
  2    x.*,
  3    first_value(global_idx) over ( partition by w_row,w_col  )  idx
  4  from
  5  (
  6  select t.*,
  7         row_number() over ( partition by w_row,w_col order by rowid) as repeat_val,
  8         rownum as global_idx
  9  from temp_mins t
 10  ) x
 11  order by idx
 12  /

BARCODE              W_R      W_COL      KEYID REPEAT_VAL GLOBAL_IDX        IDX
-------------------- --- ---------- ---------- ---------- ---------- ----------
AAAAAA-1             B            2          1          1          1          1
AAAAAA-2             B            2         11          2         46          1
AAAAAA-2             B            3         11          2         47          2
AAAAAA-1             B            3          1          1          2          2
AAAAAA-2             B            4         11          2         48          3
AAAAAA-1             B            4          1          1          3          3
AAAAAA-2             C            2         12          2         41          4
AAAAAA-1             C            2          2          1          4          4
AAAAAA-1             C            3          2          1          5          5
AAAAAA-2             C            3         12          2         42          5
AAAAAA-2             C            4         12          2         43          6
AAAAAA-1             C            4          2          1          6          6
AAAAAA-1             D            2          3          1          7          7
AAAAAA-2             D            2          5          2         44          7
AAAAAA-1             D            3          3          1          8          8
AAAAAA-2             D            3          5          2         45          8
AAAAAA-1             D            4          3          1          9          9
AAAAAA-2             D            4          5          2         25          9
AAAAAA-2             E            2          4          2         26         10
AAAAAA-1             E            2          4          1         10         10
AAAAAA-1             E            3          4          1         11         11
AAAAAA-2             E            3          4          2         27         11
AAAAAA-1             E            4          4          1         12         12
AAAAAA-2             E            4          4          2         28         12
AAAAAA-1             F            2          5          1         13         13
AAAAAA-2             F            2          3          2         29         13
AAAAAA-2             F            3          3          2         30         14
AAAAAA-1             F            3          5          1         14         14
AAAAAA-1             F            4          5          1         15         15
AAAAAA-2             F            4          3          2         31         15
AAAAAA-1             G            2          6          1         16         16
AAAAAA-2             G            2         16          2         32         16
AAAAAA-1             G            3          6          1         17         17
AAAAAA-2             G            3         16          2         33         17
AAAAAA-2             G            4         16          2         34         18
AAAAAA-1             G            4          6          1         18         18
AAAAAA-1             H            2          7          1         19         19
AAAAAA-2             H            2          7          2         35         19
AAAAAA-1             H            3          7          1         20         20
AAAAAA-2             H            3          7          2         36         20
AAAAAA-1             H            4          7          1         21         21
AAAAAA-2             H            4          7          2         37         21
AAAAAA-2             A            2       4000          2         38         22
AAAAAA-1             A            2       4000          1         22         22
AAAAAA-2             A            3       4000          2         39         23
AAAAAA-1             A            3       4000          1         23         23
AAAAAA-1             A            4       4000          1         24         24
AAAAAA-2             A            4       4000          2         40         24


Rating

  (14 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Results are not what is expected

SP, November 05, 2015 - 3:25 pm UTC

Output should be

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 



Partition should involve keyid,w_row,w_col.

The output selects all columns + a new index column calculated as I am describing below.

This is the the logic:

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)
other combinations of keyid,w_row,w_col should have their own unique indexes.


Chris Saxon
November 06, 2015 - 2:18 am UTC

I've added a new SQL to the last review - please check that, and let me know if its what you are after.

Cheers,
Connor
@connor_mc_d

order by in first_value ?

Rajeshwaran, Jeyabal, November 05, 2015 - 3:29 pm UTC

Connor - Without the order by clause in First_value function, does the results be deterministic ?

SQL> select
  2    x.*,
  3    first_value(global_idx) over ( partition by w_row,w_col  )  idx
  4  from
  5  (

Chris Saxon
November 06, 2015 - 2:12 am UTC

agreed, but my solution wasnt what the OP was after, so taking another look from scratch.

SP, November 05, 2015 - 3:50 pm UTC

Its not ordering issue.
As you see expected output: for each barcode, all the keyids needs to occur only once.

a keyid could be in more than one barcode block (here there are only 2 blocks:AAAAAA-1 and AAAAAA-2) BUT if it's w_row, w_col are same (as you see in my example) it should get the SAME index as it was assigned for earlier combo (keyid, w_row, w_col):
NOTE: barcode, w_row, w_col are always unique.

To repeat the logic again:

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)


@SP

Rajeshwaran, Jeyabal, November 05, 2015 - 3:57 pm UTC

The output selects all columns + a new index column calculated as I am describing below. 

This is the the logic: 

<i>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:</i> 


Can you explain the logic with one keyid at a time? say for example we take keyid = 4000, we have six rows like this.

rajesh@ORA10G> column comments noprint
rajesh@ORA10G> select *
  2  from temp_mins
  3  where keyid = 4000
  4  order by w_row,w_col,barcode;

BARCODE              W_R      W_COL      KEYID
-------------------- --- ---------- ----------
AAAAAA-1             A            2       4000
AAAAAA-2             A            2       4000
AAAAAA-1             A            3       4000
AAAAAA-2             A            3       4000
AAAAAA-1             A            4       4000
AAAAAA-2             A            4       4000

6 rows selected.

rajesh@ORA10G>


Now applying this logic, i get the output like this.

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.


rajesh@ORA10G> select *
  2  from temp_mins
  3  where keyid = 4000
  4  model
  5    dimension by ( row_number() over(order by keyid,w_row,w_col,barcode) rn)
  6    measures( comments,keyid,barcode,w_row,w_col,0 grp)
  7    rules
  8    ( grp[any] order by rn = case when cv(rn)=1 then 1
  9                              when keyid[cv(rn)] = keyid[cv(rn)-1] and
 10                                   w_row[cv(rn)] = w_row[cv(rn)-1] and
 11                                   w_col[cv(rn)] = w_col[cv(rn)-1] then grp[cv(rn)-1]
 12                                   else grp[cv()-1]+1 end )
 13  /

        RN COMMENTS        KEYID BARCODE    W_R      W_COL        GRP
---------- ---------- ---------- ---------- --- ---------- ----------
         1 xyzzzzz71        4000 AAAAAA-1   A            2          1
         2 xyzzzzz129       4000 AAAAAA-2   A            2          1
         3 xyzzzzz72        4000 AAAAAA-1   A            3          2
         4 xyzzzzz130       4000 AAAAAA-2   A            3          2
         5 xyzzzzz73        4000 AAAAAA-1   A            4          3
         6 xyzzzzz131       4000 AAAAAA-2   A            4          3

6 rows selected.

rajesh@ORA10G>


is this what you are looking for the keyid = 4000 ?

SP, November 05, 2015 - 5:52 pm UTC

I think the one you got there is pretty close.

Let me put in another way:

each of the barcode blocks are like a grid
so for each of the barcode blocks, grid should look like below: where A,B,C,D etc are w_row and 1,2,3,etc are w_col.
At each cross section, there is keyid. its repeated across ROWS in grid.
Index 1 is assigned for first grid row for keyid 4000;
for row2: keyid is 1 so next index is assigned; ie; 2
for row3: keyid is 2 so next index is assigned; ie; 3 and so on..

Block AAAAAA-1
1 2 3 Index
---------------------------------------------------------
A (keyid=4000) 4000 4000 1
---------------------------------------------------------
B 1 1 1 2
---------------------------------------------------------
C 2 2 2 3
---------------------------------------------------------
D 3 3 3 4
---------------------------------------------------------
E 4 4 4 5
---------------------------------------------------------
F 5 5 5 6
---------------------------------------------------------
G 6 6 6 7
---------------------------------------------------------
H 7 7 7 8
---------------------------------------------------------

For Block AAAAAA-2

1 2 3 Index
---------------------------------------------------------
A (keyid=4000) 4000 4000 1 (assign same index as above bcoz this ROW matches exacly as above for block AAAAAA-1; ie keyid, w_row, w_col are same)
---------------------------------------------------------
B 11 11 11 9 (new index bcoz keyid is new )
---------------------------------------------------------
C 12 12 12 10 (new index bcoz keyid is new )
---------------------------------------------------------
D 5 5 5 11 (though keyid 5 is repeated,its on ROW:D here,unlike ROW:F above)
---------------------------------------------------------
E 4 4 4 5 (same index bcoz 4 is repeated AND it matches the ROW:E in block above)
---------------------------------------------------------
F 3 3 3 12 (new index; same case as keyid 5)
---------------------------------------------------------
G 16 16 16 13 (new keyid so new index)
---------------------------------------------------------
H 7 7 7 8 (same logic as in case of keyids: 4000,4)
---------------------------------------------------------

Hope that clears it.
Thank you so much.

SP, November 05, 2015 - 5:58 pm UTC

I tried to draw the grid but it isnt displaying properly.
It is basically like Excel sheet grid with ROW, COLUMN headers swapped. Unlike Excel, here rows are A,B,C, etc and columns are 1,2,3 and each cell has keyid values.



SP, November 05, 2015 - 6:00 pm UTC

Even if requires using another table to hold intermediate data and run another update, it should be fine.
Chris Saxon
November 06, 2015 - 2:17 am UTC

So like this ?

SQL> select
  2    x.*,
  3    min(global_idx) over ( partition by keyid,w_row,w_col )  idx
  4  from
  5  (
  6  select t.*,
  7         row_number() over (partition by keyid,w_row,w_col order by rowid) as repeat_val,
  8         rownum as global_idx
  9  from temp_mins t
 10  ) x
 11  order by idx
 12  /

BARCODE              W_R      W_COL      KEYID REPEAT_VAL GLOBAL_IDX        IDX
-------------------- --- ---------- ---------- ---------- ---------- ----------
AAAAAA-1             B            2          1          1          1          1
AAAAAA-1             B            3          1          1          2          2
AAAAAA-1             B            4          1          1          3          3
AAAAAA-1             C            2          2          1          4          4
AAAAAA-1             C            3          2          1          5          5
AAAAAA-1             C            4          2          1          6          6
AAAAAA-1             D            2          3          1          7          7
AAAAAA-1             D            3          3          1          8          8
AAAAAA-1             D            4          3          1          9          9
AAAAAA-1             E            2          4          1         10         10
AAAAAA-2             E            2          4          2         26         10
AAAAAA-1             E            3          4          1         11         11
AAAAAA-2             E            3          4          2         27         11
AAAAAA-2             E            4          4          2         28         12
AAAAAA-1             E            4          4          1         12         12
AAAAAA-1             F            2          5          1         13         13
AAAAAA-1             F            3          5          1         14         14
AAAAAA-1             F            4          5          1         15         15
AAAAAA-1             G            2          6          1         16         16
AAAAAA-1             G            3          6          1         17         17
AAAAAA-1             G            4          6          1         18         18
AAAAAA-2             H            2          7          2         35         19
AAAAAA-1             H            2          7          1         19         19
AAAAAA-2             H            3          7          2         36         20
AAAAAA-1             H            3          7          1         20         20
AAAAAA-2             H            4          7          2         37         21
AAAAAA-1             H            4          7          1         21         21
AAAAAA-1             A            2       4000          1         22         22
AAAAAA-2             A            2       4000          2         38         22
AAAAAA-1             A            3       4000          1         23         23
AAAAAA-2             A            3       4000          2         39         23
AAAAAA-2             A            4       4000          2         40         24
AAAAAA-1             A            4       4000          1         24         24
AAAAAA-2             D            4          5          1         25         25
AAAAAA-2             F            2          3          1         29         29
AAAAAA-2             F            3          3          1         30         30
AAAAAA-2             F            4          3          1         31         31
AAAAAA-2             G            2         16          1         32         32
AAAAAA-2             G            3         16          1         33         33
AAAAAA-2             G            4         16          1         34         34
AAAAAA-2             C            2         12          1         41         41
AAAAAA-2             C            3         12          1         42         42
AAAAAA-2             C            4         12          1         43         43
AAAAAA-2             D            2          5          1         44         44
AAAAAA-2             D            3          5          1         45         45
AAAAAA-2             B            2         11          1         46         46
AAAAAA-2             B            3         11          1         47         47
AAAAAA-2             B            4         11          1         48         48

48 rows selected.


for a single key-id at a time - possible ?!?!?!

Rajeshwaran, Jeyabal, November 06, 2015 - 3:05 am UTC

Let me put in another way:

each of the barcode blocks are like a grid
so for each of the barcode blocks, grid should look like below: where A,B,C,D etc are w_row and 1,2,3,etc are w_col.
At each cross section, there is keyid. its repeated across ROWS in grid.
Index 1 is assigned for first grid row for keyid 4000;


See this, you tell us how to plot the rows, but what qualifies the "first grid row" ? how do you sort them? (on which column and what values ?)

can you start with a single key-id at a time? and explain things in Readable format ? (like how i did for key-id=4000?, help me to understand what am i missing there)

SP, November 06, 2015 - 3:47 am UTC

Sorted by barcode, w_row, w_col

I will try to draw grid again

Block for barcode AAAAAA-1

Block for barcode AAAAAA-1

| 1 | 2 | 3 | Indx
----|-------------|------------------------------------
A | 4000 | 4000 | 4000 | 1 =>new index for keyid 4000
----|-------------|------------------------------------
B | 1 | 1 | 1 | 2 =>new index for keyid 1
----|-------------|------------------------------------
C | 2 | 2 | 2 | 3 =>new index for keyid 2
----|-------------|------------------------------------
D | 3 | 3 | 3 | 4 =>new index for keyid 3
----|-------------|------------------------------------
E | 4 | 4 | 4 | 5 =>new index for keyid 4
----|-------------|------------------------------------
F | 5 | 5 | 5 | 6 =>new index for keyid 5
----|-------------|------------------------------------
G | 6 | 6 | 6 | 7 =>new index for keyid 6
----|-------------|------------------------------------
H | 7 | 7 | 7 | 8 =>new index for keyid 7
so far each of the keyids have got new index because there were no repeats of keyid above


Block for barcode AAAAAA-2

| 1 | 2 | 3 | Indx
----|-------------|------------------------------------
A | 4000 | 4000 | 4000 | 1 =>reuse index for keyid 4000 bcoz we have EXACT same grid row(w_row, w_col, keyid combo)in previous block where 4000 was found
----|-------------|------------------------------------
B | 11 | 11 | 11 | 9 =>new index for keyid 11
----|-------------|------------------------------------
C | 12 | 12 | 12 | 10 =>new index for keyid 12
----|-------------|------------------------------------
D | 5 | 5 | 5 | 11 =>repeat of 5 but this grid row doesnt match (w_row, w_col, keyid combo) with any previous,so new index
----|-------------|------------------------------------
E | 4 | 4 | 4 | 5 =>repeat of 4 but this grid row has EXACT match (same case as 4000) with previous,so reuse index
----|-------------|------------------------------------
F | 3 | 3 | 3 | 12 =>repeat of keyid 3 but this grid row doesnt match (w_row, w_col, keyid combo) with any previous,so new index
----|-------------|------------------------------------
G | 16 | 16 | 16 | 13 =>new index for keyid 16
----|-------------|------------------------------------
H | 7 | 7 | 7 | 8 =>repeat of 7 but this grid row has EXACT match (same case as 4000) in previous block,so reuse index


Thank you very much for helping out.

Please check if this helps.

A reader, November 06, 2015 - 5:50 am UTC

rajesh@ORA11G> set feedback off
rajesh@ORA11G> drop table t purge;
rajesh@ORA11G> create table t(barcode varchar2(10),
  2  w_row varchar2(3),
  3  w_col int,
  4  c1 int,c2 int);
rajesh@ORA11G> insert into t(barcode,w_row,w_col,c1,c2) values('AAAAAA-1','A',4000,4000,4000);
rajesh@ORA11G> insert into t(barcode,w_row,w_col,c1,c2) values('AAAAAA-1','B',1,1,1);
rajesh@ORA11G> insert into t(barcode,w_row,w_col,c1,c2) values('AAAAAA-1','C',2,2,2);
rajesh@ORA11G> insert into t(barcode,w_row,w_col,c1,c2) values('AAAAAA-1','D',3,3,3);
rajesh@ORA11G> insert into t(barcode,w_row,w_col,c1,c2) values('AAAAAA-1','E',4,4,4);
rajesh@ORA11G> insert into t(barcode,w_row,w_col,c1,c2) values('AAAAAA-1','F',5,5,5);
rajesh@ORA11G> insert into t(barcode,w_row,w_col,c1,c2) values('AAAAAA-1','G',6,6,6);
rajesh@ORA11G> insert into t(barcode,w_row,w_col,c1,c2) values('AAAAAA-1','H',7,7,7);
rajesh@ORA11G> insert into t(barcode,w_row,w_col,c1,c2) values('AAAAAA-2','A',4000,4000,4000);
rajesh@ORA11G> insert into t(barcode,w_row,w_col,c1,c2) values('AAAAAA-2','B',11,11,11);
rajesh@ORA11G> insert into t(barcode,w_row,w_col,c1,c2) values('AAAAAA-2','C',12,12,12);
rajesh@ORA11G> insert into t(barcode,w_row,w_col,c1,c2) values('AAAAAA-2','D',5,5,5);
rajesh@ORA11G> insert into t(barcode,w_row,w_col,c1,c2) values('AAAAAA-2','E',4,4,4);
rajesh@ORA11G> insert into t(barcode,w_row,w_col,c1,c2) values('AAAAAA-2','F',3,3,3);
rajesh@ORA11G> insert into t(barcode,w_row,w_col,c1,c2) values('AAAAAA-2','G',16,16,16);
rajesh@ORA11G> insert into t(barcode,w_row,w_col,c1,c2) values('AAAAAA-2','H',7,7,7);
rajesh@ORA11G> commit;
rajesh@ORA11G> set feedback on
rajesh@ORA11G>
rajesh@ORA11G> select barcode,w_row,w_col,x idx
  2  from (
  3  select *
  4  from (
  5  select barcode,w_row,w_col,c1,c2,
  6    decode(rnk,1,rnk2) grp,cnt
  7  from (
  8  select t.*,
  9    dense_rank() over(order by barcode) rnk,
 10    row_number() over(order by barcode,w_row,w_col) rnk2,
 11    count(*) over(partition by barcode) cnt
 12  from t
 13       )
 14       )
 15  model
 16    dimension by ( row_number() over( order by w_row,barcode,w_col ) rn)
 17    measures(barcode,w_row,w_col,c1,c2,grp,cnt,0 x)
 18    rules iterate(1000) until(barcode[iteration_number+1] is null)
 19    ( x[iteration_number+1]  = case when grp[cv()] is not null then grp[cv()]
 20                                when grp[cv()] is null and
 21                                    w_row[cv()] = w_row[cv()-1] and
 22                                    w_col[cv()] = w_col[cv()-1] then grp[cv()-1]
 23                                when grp[cv()] is null and
 24                                  ( w_row[cv()] <> w_row[cv()-1] or
 25                                    w_col[cv()] <> w_col[cv()-1] ) then cnt[1] +1 end ,
 26     cnt[1] = case  when grp[iteration_number+1] is null and
 27                     ( w_row[iteration_number+1] <> w_row[iteration_number] or
 28                       w_col[iteration_number+1] <> w_col[iteration_number] ) then cnt[1]+1 else cnt[1] end )
 29      )
 30  where barcode is not null
 31  order by barcode,w_row,w_col
 32  /

BARCODE    W_R      W_COL        IDX
---------- --- ---------- ----------
AAAAAA-1   A         4000          1
AAAAAA-1   B            1          2
AAAAAA-1   C            2          3
AAAAAA-1   D            3          4
AAAAAA-1   E            4          5
AAAAAA-1   F            5          6
AAAAAA-1   G            6          7
AAAAAA-1   H            7          8
AAAAAA-2   A         4000          1
AAAAAA-2   B           11          9
AAAAAA-2   C           12         10
AAAAAA-2   D            5         11
AAAAAA-2   E            4          5
AAAAAA-2   F            3         12
AAAAAA-2   G           16         13
AAAAAA-2   H            7          8

16 rows selected.

rajesh@ORA11G>

@SP - Please check if this helps

Rajeshwaran, Jeyabal, November 06, 2015 - 5:53 am UTC

rajesh@ORA11G> set feedback off
rajesh@ORA11G> drop table t purge;
rajesh@ORA11G> create table t(barcode varchar2(10),
  2  w_row varchar2(3),
  3  w_col int,
  4  c1 int,c2 int);
rajesh@ORA11G> insert into t(barcode,w_row,w_col,c1,c2) values('AAAAAA-1','A',4000,4000,4000);
rajesh@ORA11G> insert into t(barcode,w_row,w_col,c1,c2) values('AAAAAA-1','B',1,1,1);
rajesh@ORA11G> insert into t(barcode,w_row,w_col,c1,c2) values('AAAAAA-1','C',2,2,2);
rajesh@ORA11G> insert into t(barcode,w_row,w_col,c1,c2) values('AAAAAA-1','D',3,3,3);
rajesh@ORA11G> insert into t(barcode,w_row,w_col,c1,c2) values('AAAAAA-1','E',4,4,4);
rajesh@ORA11G> insert into t(barcode,w_row,w_col,c1,c2) values('AAAAAA-1','F',5,5,5);
rajesh@ORA11G> insert into t(barcode,w_row,w_col,c1,c2) values('AAAAAA-1','G',6,6,6);
rajesh@ORA11G> insert into t(barcode,w_row,w_col,c1,c2) values('AAAAAA-1','H',7,7,7);
rajesh@ORA11G> insert into t(barcode,w_row,w_col,c1,c2) values('AAAAAA-2','A',4000,4000,4000);
rajesh@ORA11G> insert into t(barcode,w_row,w_col,c1,c2) values('AAAAAA-2','B',11,11,11);
rajesh@ORA11G> insert into t(barcode,w_row,w_col,c1,c2) values('AAAAAA-2','C',12,12,12);
rajesh@ORA11G> insert into t(barcode,w_row,w_col,c1,c2) values('AAAAAA-2','D',5,5,5);
rajesh@ORA11G> insert into t(barcode,w_row,w_col,c1,c2) values('AAAAAA-2','E',4,4,4);
rajesh@ORA11G> insert into t(barcode,w_row,w_col,c1,c2) values('AAAAAA-2','F',3,3,3);
rajesh@ORA11G> insert into t(barcode,w_row,w_col,c1,c2) values('AAAAAA-2','G',16,16,16);
rajesh@ORA11G> insert into t(barcode,w_row,w_col,c1,c2) values('AAAAAA-2','H',7,7,7);
rajesh@ORA11G> commit;
rajesh@ORA11G> set feedback on
rajesh@ORA11G>
rajesh@ORA11G> select barcode,w_row,w_col,x idx
  2  from (
  3  select *
  4  from (
  5  select barcode,w_row,w_col,c1,c2,
  6    decode(rnk,1,rnk2) grp,cnt
  7  from (
  8  select t.*,
  9    dense_rank() over(order by barcode) rnk,
 10    row_number() over(order by barcode,w_row,w_col) rnk2,
 11    count(*) over(partition by barcode) cnt
 12  from t
 13       )
 14       )
 15  model
 16    dimension by ( row_number() over( order by w_row,barcode,w_col ) rn)
 17    measures(barcode,w_row,w_col,c1,c2,grp,cnt,0 x)
 18    rules iterate(1000) until(barcode[iteration_number+1] is null)
 19    ( x[iteration_number+1]  = case when grp[cv()] is not null then grp[cv()]
 20                                when grp[cv()] is null and
 21                                    w_row[cv()] = w_row[cv()-1] and
 22                                    w_col[cv()] = w_col[cv()-1] then grp[cv()-1]
 23                                when grp[cv()] is null and
 24                                  ( w_row[cv()] <> w_row[cv()-1] or
 25                                    w_col[cv()] <> w_col[cv()-1] ) then cnt[1] +1 end ,
 26     cnt[1] = case  when grp[iteration_number+1] is null and
 27                     ( w_row[iteration_number+1] <> w_row[iteration_number] or
 28                       w_col[iteration_number+1] <> w_col[iteration_number] ) then cnt[1]+1 else cnt[1] end )
 29      )
 30  where barcode is not null
 31  order by barcode,w_row,w_col
 32  /

BARCODE    W_R      W_COL        IDX
---------- --- ---------- ----------
AAAAAA-1   A         4000          1
AAAAAA-1   B            1          2
AAAAAA-1   C            2          3
AAAAAA-1   D            3          4
AAAAAA-1   E            4          5
AAAAAA-1   F            5          6
AAAAAA-1   G            6          7
AAAAAA-1   H            7          8
AAAAAA-2   A         4000          1
AAAAAA-2   B           11          9
AAAAAA-2   C           12         10
AAAAAA-2   D            5         11
AAAAAA-2   E            4          5
AAAAAA-2   F            3         12
AAAAAA-2   G           16         13
AAAAAA-2   H            7          8

16 rows selected.

rajesh@ORA11G>

SP, you didn't make it easy

Stew Ashton, November 06, 2015 - 5:56 am UTC

Hi SP,

You started by saying there were 14 indexes, then you changed it to 13. 13 is right, but you should have said that you were correcting a mistake.

Also, in the output you didn't say explicitly that you were only showing column 2.

Anyway, I think you just want DENSE_RANK() over(order by w_row, keyid) - except that you want the result of DENSE_RANK to be reordered.

Here's my shot:
select barcode, w_row, w_col, keyid,
dense_rank() over(order by min_barcode, dr) idx
from (
  select barcode, w_row, w_col, keyid, dr,
  min(barcode) over (partition by dr) min_barcode
  from (
    select barcode, w_row, w_col, keyid,
    dense_rank() over(order by w_row, keyid) dr
    from temp_mins
    where w_col = 2
  )
)
order by 1,2;

BARCODE              W_R      W_COL      KEYID        IDX
-------------------- --- ---------- ---------- ----------
AAAAAA-1             A            2       4000          1
AAAAAA-1             B            2          1          2
AAAAAA-1             C            2          2          3
AAAAAA-1             D            2          3          4
AAAAAA-1             E            2          4          5
AAAAAA-1             F            2          5          6
AAAAAA-1             G            2          6          7
AAAAAA-1             H            2          7          8
AAAAAA-2             A            2       4000          1
AAAAAA-2             B            2         11          9
AAAAAA-2             C            2         12         10
AAAAAA-2             D            2          5         11
AAAAAA-2             E            2          4          5
AAAAAA-2             F            2          3         12
AAAAAA-2             G            2         16         13
AAAAAA-2             H            2          7          8

@ Stew Ashton

SP, November 06, 2015 - 1:58 pm UTC

records are ordered by barcode, we_row, w_col.
w_col=2 gets selected because that's the FIRST w_col in the first record.

instead of 2 it could have been 3 or 4.

Its very useful SQL though.

Regarding indexes (13 or 14), that was an example. since it gets generated (purpose of the SQL), so I guess it was immaterial.

Thank you very much

@Stew Ashton

SP, November 06, 2015 - 2:28 pm UTC

It works perfectly.

Thank you all for chipping in.
Really appreciate it.


More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.