Search code examples
mysqlselectinsert-update

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.

Solution

  • 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;