MySQL 5.7

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

CREATE TABLE Purchasing (
    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"),

("C007", "Offered", NULl, "900"),
("C008", "Offered", NULl, "400");



SELECT
campaign, 
main_event,
quantity
FROM Purchasing p
WHERE main_event = (SELECT p2.main_event
                    FROM Purchasing p2
                    WHERE p2.campaign = p.campaign
                    ORDER BY field(p2.main_event, 'Storing', 'Ordered', 'Offered')
                    LIMIT 1
                   );


campaign
main_event
quantity


C001
Storing
465

C001
Storing
440

C001
Storing
445

C002
Storing
690

C002
Storing
692

C003
Storing
320

C004
Storing
740

C005
Storing
226

C006
Ordered
105

C007
Offered
900

C008
Offered
400


SELECT
campaign, 
sub_event,
quantity
FROM Purchasing p
WHERE sub_event = (SELECT p3.sub_event
                    FROM Purchasing p3
                    WHERE p3.campaign = p.campaign
                    AND p3.sub_event IS NOT NULL
                    ORDER BY field(p3.sub_event, 'Completed', 'Recorded', 'Delivered')
                    LIMIT 1
                   );


campaign
sub_event
quantity


C001
Completed
445

C002
Recorded
692

C003
Delivered
320

C004
Delivered
740

C005
Delivered
226


SELECT p2.sub_event
FROM Purchasing p2
WHERE p2.sub_event IS NOT NULL
ORDER BY field(p2.sub_event, 'Completed', 'Recorded', 'Delivered')
LIMIT 1;


sub_event


Completed


SELECT 
p2.sub_event
FROM Purchasing p2
WHERE p2.sub_event IS NOT NULL
AND Campaign ="C003";


sub_event


Delivered


SELECT p2.sub_event, 
field(p2.sub_event, 'Completed', 'Recorded', 'Delivered') as Position
FROM Purchasing p2
WHERE p2.sub_event IS NOT NULL
ORDER BY 2;


sub_event
Position


Completed
1

Recorded
2

Recorded
2

Delivered
3

Delivered
3

Delivered
3

Delivered
3

Delivered
3

campaign	main_event	quantity
C001	Storing	465
C001	Storing	440
C001	Storing	445
C002	Storing	690
C002	Storing	692
C003	Storing	320
C004	Storing	740
C005	Storing	226
C006	Ordered	105
C007	Offered	900
C008	Offered	400

campaign	sub_event	quantity
C001	Completed	445
C002	Recorded	692
C003	Delivered	320
C004	Delivered	740
C005	Delivered	226