By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE test ( id INT,
type VARCHAR(192),
value INT,
PRIMARY KEY (id, type) );
-- create additional service table which will help
CREATE TABLE test_sevice ( type VARCHAR(192),
id INT AUTO_INCREMENT,
PRIMARY KEY (type, id) ) ENGINE = MyISAM;
-- create trigger which wil generate id value for new row
CREATE TRIGGER tr_bi_test_autoincrement
BEFORE INSERT
ON test
FOR EACH ROW
BEGIN
INSERT INTO test_sevice (type) VALUES (NEW.type);
SET NEW.id = LAST_INSERT_ID();
END
-- now let's test
INSERT INTO test (type, value) VALUES
('type 1',1),
('type 1',2),
('type 2',3),
('type 3',4),
('type 2',5),
('type 3',6),
('type 1',7),
('type 2',8),
('type 1',9),
('type 4',10);
SELECT * FROM test ORDER BY value; -- in insertion order
SELECT * FROM test ORDER BY type, id; -- in enumerated order
| id | type | value |
|---|---|---|
| 1 | type 1 | 1 |
| 2 | type 1 | 2 |
| 1 | type 2 | 3 |
| 1 | type 3 | 4 |
| 2 | type 2 | 5 |
| 2 | type 3 | 6 |
| 3 | type 1 | 7 |
| 3 | type 2 | 8 |
| 4 | type 1 | 9 |
| 1 | type 4 | 10 |
| id | type | value |
|---|---|---|
| 1 | type 1 | 1 |
| 2 | type 1 | 2 |
| 3 | type 1 | 7 |
| 4 | type 1 | 9 |
| 1 | type 2 | 3 |
| 2 | type 2 | 5 |
| 3 | type 2 | 8 |
| 1 | type 3 | 4 |
| 2 | type 3 | 6 |
| 1 | type 4 | 10 |
SELECT * FROM test_sevice;
-- clear excess rows from service table
DELETE t1
FROM test_sevice t1
JOIN test_sevice t2 USING (type)
WHERE t1.id < t2.id;
SELECT * FROM test_sevice;
| type | id |
|---|---|
| type 1 | 1 |
| type 1 | 2 |
| type 1 | 3 |
| type 1 | 4 |
| type 2 | 1 |
| type 2 | 2 |
| type 2 | 3 |
| type 3 | 1 |
| type 3 | 2 |
| type 4 | 1 |
| type | id |
|---|---|
| type 1 | 4 |
| type 2 | 3 |
| type 3 | 2 |
| type 4 | 1 |
-- check that everything works correctly
INSERT INTO test (type, value) VALUES
('type 1',11),
('type 5',12),
('type 2',13);
SELECT * FROM test ORDER BY value; -- in insertion order
SELECT * FROM test ORDER BY type, id; -- in enumerated order
| id | type | value |
|---|---|---|
| 1 | type 1 | 1 |
| 2 | type 1 | 2 |
| 1 | type 2 | 3 |
| 1 | type 3 | 4 |
| 2 | type 2 | 5 |
| 2 | type 3 | 6 |
| 3 | type 1 | 7 |
| 3 | type 2 | 8 |
| 4 | type 1 | 9 |
| 1 | type 4 | 10 |
| 5 | type 1 | 11 |
| 1 | type 5 | 12 |
| 4 | type 2 | 13 |
| id | type | value |
|---|---|---|
| 1 | type 1 | 1 |
| 2 | type 1 | 2 |
| 3 | type 1 | 7 |
| 4 | type 1 | 9 |
| 5 | type 1 | 11 |
| 1 | type 2 | 3 |
| 2 | type 2 | 5 |
| 3 | type 2 | 8 |
| 4 | type 2 | 13 |
| 1 | type 3 | 4 |
| 2 | type 3 | 6 |
| 1 | type 4 | 10 |
| 1 | type 5 | 12 |