Question: How would you iterate over the previous result (referenced below) over for each of the values in column b3?
MariaDB Fiddle (this includes prior fiddle information to solve the previous question): https://dbfiddle.uk/?rdbms=mariadb_10.5&fiddle=222f694a36cc41131fe558438e1d6ccd
NOTE: this is a continuation from a problem referenced here: Update SET N + 1 equal to a row
Requesting a solution when adding 1 more dimension of complexity.
Input Table: 3 columns (iteration for each b3)
b1 b2 b3 1 X P1 2 Z P1 3 X P1 4 Y P1 5 Z P1 6 X P1 7 Y P1 8 X P2 9 Z P3 10 X P3 11 Z P3 12 X P3 13 Z P2 14 Z P3
Desired Result: 3 columns (iteration for each b3)
b1 b2 b3 1 X P1 2 Y P1 3 X P1 4 Y P1 5 Z P1 6 X P1 7 Y P1 8 X P2 9 Z P3 10 X P3 11 Y P3 12 X P3 13 Y P2 14 Y P3
Simple iteration for reference on this data:
— This code increments count and then iterates over each value of b3
— Then sets the resulting values to b2.
UPDATE b JOIN( SELECT b1, row_number() over (partition by b3 order by b1) rn FROM b) n on n.b1 = b.b1 SET b2 = rn;