How to Assign Sequential Value to Each Row in a Group-CodePudding

I have a MySQL table with data for two groups (fruits and animals), and I need to assign a sequential value starting at 1 within each group

The initial state of the data is:

Fruit   Apple   NULL
Fruit   Orange  NULL
Fruit   Banana  NULL
Animal  Dog     NULL
Animal  Cat     NULL
Animal  Horse   NULL

The desired result is:

Fruit   Apple   1
Fruit   Orange  2
Fruit   Banana  3
Animal  Dog     1
Animal  Cat     2
Animal  Horse   3

(it doesn't matter if the results are alphabetized; only that there is a unique number starting at 1 within each group)

So far, I have been able to assign a sequential value to each row ACROSS THE WHOLE TABLE (i.e., ranging from 1 to 6), but I'd like to ask for guidance on how to reset the counter back to 1 when the group changes from "fruit" to "animal"

Here is the setup:

CREATE TABLE TempData (GroupName VARCHAR(16), ItemName VARCHAR(16), Sequence INT );
INSERT INTO TempData VALUES ('Fruit','Apple',NULL), ('Fruit','Orange',NULL), ('Fruit','Banana',NULL), ('Animal','Dog',NULL), ('Animal','Cat',NULL), ('Animal','Horse',NULL);

And here is my current script

UPDATE TempData
INNER JOIN (  SELECT GroupName, ItemName, (@RowOrder := @RowOrder   1) NewValue 
              FROM TempData
              INNER JOIN (SELECT @RowOrder := 0) VariableSet ) RowSequencing ON TempData.GroupName = RowSequencing.GroupName AND TempData.ItemName = RowSequencing.ItemName
SET TempData.Sequence = RowSequencing.NewValue;

Any help in detecting when the group name changes such that assigned sequence number starts back at 1 would be very appreciated.

Thanks

CodePudding user response：

I would suggest using ROW_NUMBER() if you are running MySQL 8 :

SELECT GroupName, ItemName,
       ROW_NUMBER() OVER (PARTITION BY GroupName ORDER BY ItemName) Sequence
FROM TempData
ORDER BY GroupName, ItemName;

I would also suggest not saving the sequence in an actual table column. This is because the sequence will logically change every time data is added or deleted, which might force you to update again.

If you must do the update, then we can use an update join:

UPDATE TempData t1
INNER JOIN
(
    SELECT GroupName, ItemName,
       ROW_NUMBER() OVER (PARTITION BY GroupName ORDER BY ItemName) rn
    FROM TempData
) t2
    ON t2.GroupName = t1.GroupName AND
       t2.ItemName = t1.ItemName
SET
    t1.Sequence = t2.rn;