MySQL – Select Increment Group Row Number

MySQLquery

i had a table like following:

OBJECTID            FRUIT
----------------------------
XA13AC              Apple
HAS13X              Grape
UTHQ1G              Orange
YQ1GXQ              Kiwi
OQP1GV              Apple
GPQ1VA              Grape
GQ1GQ1              Manggo
GYX1GQ              Banana
OPI1G2              Apple

Table:

CREATE TABLE `FRUITS` (
  `OBJECTID` varchar(6) DEFAULT NULL,
  `FRUIT` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Data:

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');

i tried following SQL not working:

SELECT *,
             ( 
                CASE FRUIT 
                WHEN @curType 
                THEN @curRow := @curRow + 1 
                ELSE @curRow := 1 AND @curType := FRUIT END
              ) + 1 AS GROUP_NUM
FROM FRUITS 
JOIN (SELECT @curRow := 0, @curType := '') r
GROUP BY FRUIT

expected result:

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

trying to group the fruit by name and its increment group_num on different fruit name.

any help would be great.
Thanks

Best Answer

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