MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE `FRUITS` (
  `OBJECTID` varchar(6) DEFAULT NULL,
  `FRUIT` varchar(255) DEFAULT NULL
)
insert into `FRUITS` (`OBJECTID`, `FRUIT`) values('XA13AC','Apple');
insert into `FRUITS` (`OBJECTID`, `FRUIT`) values('HAS13X','Grape');
insert into `FRUITS` (`OBJECTID`, `FRUIT`) values('UTHQ1G','Orange');
insert into `FRUITS` (`OBJECTID`, `FRUIT`) values('YQ1GXQ','Kiwi');
insert into `FRUITS` (`OBJECTID`, `FRUIT`) values('OQP1GV','Apple');
insert into `FRUITS` (`OBJECTID`, `FRUIT`) values('GPQ1VA','Grape');
insert into `FRUITS` (`OBJECTID`, `FRUIT`) values('GQ1GQ1','Manggo');
insert into `FRUITS` (`OBJECTID`, `FRUIT`) values('GYX1GQ','Banana');
insert into `FRUITS` (`OBJECTID`, `FRUIT`) values('OPI1G2','Apple');
    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


OBJECTID
GROUP_NUM
FRUIT


XA13AC
1
Apple

OQP1GV
1
Apple

OPI1G2
1
Apple

GYX1GQ
2
Banana

HAS13X
3
Grape

GPQ1VA
3
Grape

YQ1GXQ
4
Kiwi

GQ1GQ1
5
Manggo

UTHQ1G
6
Orange


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


OBJECTID
FRUIT
GROUP_NUM


XA13AC
Apple
1

OQP1GV
Apple
1

OPI1G2
Apple
1

GYX1GQ
Banana
2

HAS13X
Grape
3

GPQ1VA
Grape
3

YQ1GXQ
Kiwi
4

GQ1GQ1
Manggo
5

UTHQ1G
Orange
6

OBJECTID	GROUP_NUM	FRUIT
XA13AC	1	Apple
OQP1GV	1	Apple
OPI1G2	1	Apple
GYX1GQ	2	Banana
HAS13X	3	Grape
GPQ1VA	3	Grape
YQ1GXQ	4	Kiwi
GQ1GQ1	5	Manggo
UTHQ1G	6	Orange