Untitled

MySQL - How to update atable based on an internal ordering of the table
SELECT * FROM mytable ORDER BY country_id, category

+------------+----------+-------+
| country_id | category | order |
+------------+----------+-------+
|          1 |        A |     0 |
|          1 |        B |     0 |
|          1 |        F |     0 |
|          3 |        A |     0 |
|          3 |        C |     0 |
|          5 |        B |     0 |
|          5 |        L |     0 |
|          5 |        P |     0 |
+------------+----------+-------+

+------------+----------+-------+
| country_id | category | order |
+------------+----------+-------+
|          1 |        A |     1 |
|          1 |        B |     2 |
|          1 |        F |     3 |
|          3 |        A |     1 |
|          3 |        C |     2 |
|          5 |        B |     1 |
|          5 |        L |     2 |
|          5 |        P |     3 |
+------------+----------+-------+

UPDATE mytable t1
  JOIN (
    SELECT t1.country_id, t1.category, COUNT(*) `order`
    FROM mytable t1
    LEFT JOIN mytable t2
      ON t2.country_id = t1.country_id AND t2.category <= t1.category
    GROUP BY
      t1.country_id, t1.category
  ) t2
  ON t1.country_id = t2.country_id AND t1.category = t2.category
SET t1.`order` = t2.`order`