By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
project_id INT,
project_number INT
);
CREATE TABLE generate_project_number (
project_id INT,
project_number INT AUTO_INCREMENT,
PRIMARY KEY (project_id, project_number)
) ENGINE = MyISAM;
CREATE TRIGGER generate_project_number
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
INSERT INTO generate_project_number (project_id) VALUES (NEW.project_id);
SET NEW.project_number = LAST_INSERT_ID();
DELETE FROM generate_project_number
WHERE project_id = NEW.project_id
AND project_number < NEW.project_number;
END
INSERT INTO orders (project_id) VALUES (1), (1), (2), (3), (1);
SELECT * FROM orders;
SELECT * FROM orders ORDER BY 2, 3;
SELECT * FROM generate_project_number;
Records: 5 Duplicates: 0 Warnings: 0
| id | project_id | project_number |
|---|---|---|
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 2 | 1 |
| 4 | 3 | 1 |
| 5 | 1 | 3 |
| id | project_id | project_number |
|---|---|---|
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 5 | 1 | 3 |
| 3 | 2 | 1 |
| 4 | 3 | 1 |
| project_id | project_number |
|---|---|
| 1 | 3 |
| 2 | 1 |
| 3 | 1 |