0

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

2
  • Ordering needed. Try to add ORDER BY fruit.
    – Akina
    Jul 2 '19 at 6:54
  • the group_num not populated correctly
    – Teddybugs
    Jul 2 '19 at 6:57
3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.