mysql select increment group row number
For version 5+

SELECT OBJECTID,
       CASE WHEN FRUIT = @curType 
            THEN @curRow 
            ELSE @curRow := @curRow + 1 END AS GROUP_NUM,
       @curType := FRUIT FRUIT
FROM FRUITS, (SELECT @curRow := 1, @curType := '') r
ORDER BY FRUIT


For version 8+

SELECT OBJECTID, FRUIT, DENSE_RANK() OVER (ORDER BY FRUIT) GROUP_NUM
FROM FRUITS


fiddle