MySQL 8.0

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

CREATE DATABASE zzz_test;
CREATE TABLE zzz_test.`table_raw` (
  `grp_name` VARCHAR(70) NOT NULL,
  `sub_grp` INT NOT NULL,
  `item` VARCHAR(5) NOT NULL
) ENGINE=INNODB;
INSERT INTO zzz_test.`table_raw` VALUES 
  ('GRP_A',1, 'xxx'),
  ('GRP_A',2, 'yyy'),
  ('GRP_A',1, 'yyy'),
  ('GRP_A',2, 'xxx'),
  ('GRP_B',1, 'xxx'),
  ('GRP_B',1, 'yyy'),
  ('GRP_B',2, 'yyy'),
  ('GRP_B',2, 'xxx')
;

Records: 8  Duplicates: 0  Warnings: 0


CREATE TABLE zzz_test.`table_with_index` (
  `grp_name` VARCHAR(70) NOT NULL,
  `sub_grp` INT NOT NULL,
  `item_index` INT NOT NULL,
  `item` VARCHAR(5) NOT NULL,
  PRIMARY KEY (`grp_name`,`sub_grp`,`item_index`)
) ENGINE=INNODB;
INSERT INTO zzz_test.`table_with_index`
SELECT grp_name, sub_grp,
       ROW_NUMBER() OVER (
            PARTITION BY grp_name,sub_grp 
            ORDER BY grp_name,sub_grp,item
       ) AS item_index,
       item
FROM zzz_test.`table_raw`
ORDER BY grp_name,sub_grp,item;

Records: 8  Duplicates: 0  Warnings: 0


SELECT * FROM zzz_test.`table_with_index`;


grp_name
sub_grp
item_index
item


GRP_A
1
1
xxx

GRP_A
1
2
yyy

GRP_A
2
1
xxx

GRP_A
2
2
yyy

GRP_B
1
1
xxx

GRP_B
1
2
yyy

GRP_B
2
1
xxx

GRP_B
2
2
yyy

grp_name	sub_grp	item_index	item
GRP_A	1	1	xxx
GRP_A	1	2	yyy
GRP_A	2	1	xxx
GRP_A	2	2	yyy
GRP_B	1	1	xxx
GRP_B	1	2	yyy
GRP_B	2	1	xxx
GRP_B	2	2	yyy