MySQL Fix Gap in Ordinal Sequence

Question

all. I'm working on an assignment to update the order images appear in a table. This is done with a table structure that looks like: product_key, image_key, image_order. The first two are foreign keys to other tables and the latter is an ordinal sequence in which the images will appear. In the process of selecting the images, I matched those available to a set of rules. In some cases there was no image that fit a criteria set leading to a gap in the image order. E.g.,

product_key	image_key	image_order
5692	42265	0
5692	42207	1
5692	42210	2
5692	42212	3
5692	42266	5
5692	42273	6
5692	42268	7
5692	42264	8

This is an example for a single product_key but there are over 1200 product_keys and over 11k total rows. What I need to do is find a place where row(n) product_key equals row(n-1) product_key but row(n) image_order does not equal row(n-1) image_order+1 and then change row(n) image order to row(n-1) image_order+1.

Based on an example from someone who no longer works here, I tried this:

UPDATE mytable i
    JOIN (
        SELECT ordered_id, product_key, image_order, 
            (@ROW:=(IF(@productkey=product_key, @ROW+1, 0))) AS row_order, 
            (@productkey:=product_key) AS pid 
            FROM mytable,
                (SELECT @ROW=-1, @productkey:=-1) a 
                ORDER BY product_key, image_order) b 
            ON b.ordered_id=i.ordered_id
        SET i.image_order = b.row_order
        WHERE i.image_order <> b.row_order;

But that updated image_order to 0 in all rows due to some difference between when he developed it to when I ran it.

I am looking at this example and I feel like it's close to what I need, but I haven't figured it out yet.

Here's the DBFiddle for the problem.

Any thoughts on how to cover those gaps so the final display doesn't have gaps?

TIA

Feel free to add info to this [DBFIDDLE](https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=3ec11c3fe68e9765fd21a71735fafca8) to make your question a reproducible (as in [mre]) — Luuk, Mar 24 '22 at 15:05
Thanks, @Luuk! I added a correct DBFIDDLE and updated the tag to properly reference MariaDB 10.5. — Vann.the.Red, Mar 24 '22 at 15:41
MariaDB 1.5 has ROW_NUMBER() too, see: https://dbfiddle.uk/?rdbms=mariadb_10.5&fiddle=f5949f99e28f153a95f761ac9b88e7ac (which can make your UPDATE simpler, but that's left for you ( — Luuk, Mar 24 '22 at 15:49
Okay, I see that. I see I haven't explained the problem as well as I might. What I posted was the set for one product_key, but there are over 1200 product keys in the table and over 11k total rows in the table. This has R be sequential over the full data set where it needs to reset for each new product_key. — Vann.the.Red, Mar 24 '22 at 18:01
You need to add a "PARTITION BY product_key". That tells ROW_NUMBER() to reset the counter whenever the product number changes. One of your [original links](https://stackoverflow.com/questions/58523002/insert-ordinal-number) has an example. BTW, if you're looking at MySQL examples, be sure to search for ones using v8+, which supports window functions like row_number(). — SOS, Mar 25 '22 at 02:24

score 0 · Accepted Answer · answered Mar 25 '22 at 19:02

Many thanks to @luuk and @sos! Here is the solution:

insert into mytable_2 
with image_order_update_CTE (ordered_id, product_key, image, image_order, R)
as 
(
SELECT 
   m.*, 
   row_number() over (partition by product_key order by image_order)-1 R 
FROM mytable m
ORDER BY product_key
)
select image_order_update_CTE.ordered_id, image_order_update_CTE.product_key, image_order_update_CTE.image, image_order_update_CTE.image_order, image_order_update_CTE.R from image_order_update_CTE
;

I really appreciate this community and thanks again. VtR

MySQL Fix Gap in Ordinal Sequence

1 Answers1