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+
For version 8+
fiddle