Kevin Kevin - 2 months ago 7
MySQL Question

MySQL Query - Update field with order/count but start at 1 again based on another field

I have a table of products. This table was created with a

SELECT from X ORDER by Y
query. I want to add sequential row count or order (1,2,3..).

However, I want this count to reset to 1 when the product category or vendor changes. (I'll end up with a order to sort by when querying a combination of product category and vendor).

This problem is simplification of a sub-problem related to a larger issue. So, other solutions involving php aren't relevant.

Here's a sample table:

+--------------+------------------+-----------+-----------+
| product_name | product_category | vendor_id | sortorder |
+--------------+------------------+-----------+-----------+
| Product 1 | A | 1 | 0 |
| Product 2 | A | 1 | 0 |
| Product 3 | A | 1 | 0 |
| Product 4 | B | 1 | 0 |
| Product 5 | B | 1 | 0 |
| Product 6 | C | 2 | 0 |
| Product 7 | C | 2 | 0 |
| Product 8 | C | 2 | 0 |
| Product 9 | C | 2 | 0 |
| Product 10 | C | 2 | 0 |
+--------------+------------------+-----------+-----------+


This is how it should look if the query is run successfully:

+--------------+------------------+-----------+-----------+
| product_name | product_category | vendor_id | sortorder |
+--------------+------------------+-----------+-----------+
| Product 1 | A | 1 | 1 |
| Product 2 | A | 1 | 2 |
| Product 3 | A | 1 | 3 |
| Product 4 | B | 1 | 1 |
| Product 5 | B | 1 | 2 |
| Product 6 | C | 2 | 1 |
| Product 7 | C | 2 | 2 |
| Product 8 | C | 2 | 3 |
| Product 9 | C | 2 | 1 |
| Product 10 | C | 2 | 1 |
+--------------+------------------+-----------+-----------+


I have tried a TON of different queries related to this answer, mostly to try and get this result from the initial query, but to no avail:
Using LIMIT within GROUP BY to get N results per group?

I could run a query like this to get it ordered 1,2,3,10):

SET @pos = 0;
UPDATE testtable SET sortorder = ( SELECT @pos := @pos + 1 );


But, that doesn't accomplish what I want, which is the count to start over again at 1 when the 'product_category' changes between Product 3 and Product 4.

In bad syntax, this is what I want to do:

SET @pos = 0;
UPDATE testtable SET sortorder =
// { if (product_category != [last product_category]
// OR
// if (vendor_id != [last vendor_id])
// }
// THEN SET sortorder = 1
// ELSE SET sortorder = (1+ [last sortorder]
;


Thanks as always...

EDIT-9.12.2016

Trying the solution from @Fancypants. Actually, at first it appears not to work, but it has to do with the "product_name" field sort order. It puts Product 10 before product 5 (1 comes before 5). Once I account for that by using an integer field instead, the result is perfect.

Answer

I assume you have an error in your desired result. Sortorder for Product 9 and 10 should be 4 and 5, right?

Here's how you can do it:

UPDATE t
JOIN (

    SELECT
    t.*
    , @rc := IF(@prevpc != product_category OR @prevv != vendor_id, 1, @rc + 1) AS so
    , @prevpc := product_category
    , @prevv := vendor_id
    FROM
    t
    , (SELECT @prevpc := NULL, @prevv := NULL, @rc := 0) var_init_subquery
    ORDER BY product_category, vendor_id, product_name

) sq ON t.product_name = sq.product_name
SET t.sortorder = sq.so;