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 |