How does a prioritization query work (one value is preferred over another if a criteria is met)

DB-Fiddle

CREATE TABLE Purchasing (
    Campaign VARCHAR(255),
    Event_Type VARCHAR(255),
    Quantity_Offer VARCHAR(255),
    Quantity_Order VARCHAR(255),
    Quantity_Received VARCHAR(255),
    Quantity_Stored VARCHAR(255)
);

INSERT INTO Purchasing
(Campaign, Event_Type, Quantity_Offer, Quantity_Order, Quantity_Received, Quantity_Stored)
VALUES 
("C001", "Offered", "300", NULL, NULL, NULL),
("C001", "Ordered", NULL, "350", NULL, NULL),
("C001", "Received", NULL, NULL, "348", NULL),
("C001", "Stored", NULL, NULL, NULL, "345"),

("C002", "Offered", "800", NULL, NULL, NULL),
("C002", "Ordered", NULL, "720", NULL, NULL),
("C002", "Received", NULL, NULL, "730", NULL),
("C002", "Stored", NULL, NULL, NULL, "735"),

("C003", "Offered", "600", NULL, NULL, NULL),
("C003", "Ordered", NULL, "400", NULL, NULL),
("C003", "Received", NULL, NULL, "410", NULL),

("C004", "Offered", "150", NULL, NULL, NULL),
("C004", "Ordered", NULL, "200", NULL, NULL),

("C005", "Offered", "430", NULL, NULL, NULL),
("C005", "Ordered", NULL, "440", NULL, NULL),

("C006", "Offered", "520", NULL, NULL, NULL),
("C006", "Ordered", NULL, "670", NULL, NULL),

("C007", "Offered", "330", NULL, NULL, NULL),
("C008", "Offered", "780", NULL, NULL, NULL);

The table displays different campaigns and their status within the purchasing process using event_types.
The process is as the following:

Stored > Received > Ordered > Offered

---

Based on this structure the result should look like this:

Campaign        Event_Type         Quantity
C001              Stored              345
C002              Stored              735
C003              Received            410
C004              Ordered             200
C005              Ordered             440
C006              Ordered             670
C007              Offered             330
C008              Offered             780

In order to achieve this I go with the prioritization query described in the question here:

select 
campaign, 
event_type,
coalesce(Quantity_Offered, Quantity_Ordered, Quantity_Received, Quantity_Stored) AS Quantity
from Purchasing p
where event_type = (select p2.event_type
                    from Purchasing p2
                    where p2.campaign = p.campaign
                    order by field(p2.event_type, 'Stored', 'Received', 'Ordered', 'Offered')
                    limit 1
                   );

This query gives me exactly the result I need.
However, I am wondering how this query actually works because when I run only this part:

select p2.event_type
from Purchasing p2
order by field(p2.event_type, 'Stored', 'Received', 'Ordered', 'Offered')
limit 1;

The result is just:

event_type
Stored

How does the query know which of the event_type comes after stored?

Answer

DB Fiddle

Step 1:
The correlated subquery – in this case WHERE p2.campaign = p.campaign – goes through all campaigns and identifies all available event_types per campaign.
Basically, the inner query is re-executed for each campaign

SELECT 
p2.event_type
FROM Purchasing p2
WHERE Campaign ="C003";

https://en.wikipedia.org/wiki/Correlated_subquery

Step 2:
Afterwards all available results per campaign are ordered by the defined hierarchy of the event_type in the field function.

SELECT p2.event_type, 
field(event_type, 'Stored', 'Received', 'Ordered', 'Offered') as Position_in_Field
FROM Purchasing p2
ORDER BY 2;

https://www.w3resource.com/mysql/string-functions/mysql-field-function.php

Step 3:
Finally, the LIMIT 1 only leaves the highest available event_type in the result and thus the WHERE clause of the outer query gets the highest event_type per campaign.

SELECT 
p2.event_type
FROM Purchasing p2
WHERE Campaign ="C003"
ORDER BY field(p2.event_type, 'Stored', 'Received', 'Ordered', 'Offered')
LIMIT 1;