By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Purchasing (
purchasing_id int auto_increment primary key,
campaign VARCHAR(255),
main_event VARCHAR(255),
sub_event VARCHAR(255),
quantity VARCHAR(255)
);
INSERT INTO Purchasing
(campaign, main_event, sub_event, quantity)
VALUES
("C001", "Offered", NULL, "500"),
("C001", "Ordered", NULL, "450"),
("C001", "Storing", "Delivered", "465"),
("C001", "Storing", "Recorded", "440"),
("C001", "Storing", "Completed", "445"),
("C002", "Offered", NULL, "600"),
("C002", "Ordered", NULL, "700"),
("C002", "Storing", "Delivered", "690"),
("C002", "Storing", "Recorded", "692"),
("C003", "Offered", NULL, "300"),
("C003", "Ordered", NULL, "250"),
("C003", "Storing", "Delivered", "320"),
("C004", "Offered", NULL, "800"),
("C004", "Ordered", NULL, "870"),
("C004", "Storing", "Delivered", "740"),
("C005", "Offered", NULL, "240"),
("C005", "Ordered", NULL, "250"),
("C005", "Storing", "Delivered", "226"),
("C006", "Offered", NULL, "100"),
("C006", "Ordered", NULL, "105"),
Records: 22 Duplicates: 0 Warnings: 0
SELECT p.*
FROM Purchasing p
WHERE p.purchasing_id = (SELECT p2.purchasing_id
FROM Purchasing p2
WHERE p2.campaign = p.campaign
ORDER BY field(p2.main_event, 'Storing', 'Ordered', 'Offered'),
field(p2.sub_event, 'Completed', 'Recorded', 'Delivered')
LIMIT 1
);
| purchasing_id | campaign | main_event | sub_event | quantity |
|---|---|---|---|---|
| 5 | C001 | Storing | Completed | 445 |
| 9 | C002 | Storing | Recorded | 692 |
| 12 | C003 | Storing | Delivered | 320 |
| 15 | C004 | Storing | Delivered | 740 |
| 18 | C005 | Storing | Delivered | 226 |
| 20 | C006 | Ordered | null | 105 |
| 21 | C007 | Offered | null | 900 |
| 22 | C008 | Offered | null | 400 |