By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
| version() |
|---|
| 8.0.19 |
CREATE TABLE purchasing (
event_type VARCHAR(255),
product VARCHAR(255),
quantity VARCHAR(255)
);
INSERT INTO purchasing
(event_type, product, quantity)
VALUES
("Offer", "Product_A", "300"),
("Offer", "Product_B", "200"),
("Offer", "Product_C", "500"),
("Offer", "Product_D", "400"),
("Offer", "Product_E", "600"),
("Order", "Product_B", "250"),
("Order", "Product_C", "450");
select product, event_type, quantity
from
(
select p.*,
row_number() over (partition by product
order by case event_type
when 'Accept' then 1
when 'Order' then 2
when 'Offer' then 3
when 'Reject' then 4
end) as rn
from purchasing p
) ranked
where rn = 1;
| product | event_type | quantity |
|---|---|---|
| Product_A | Offer | 300 |
| Product_B | Order | 250 |
| Product_C | Order | 450 |
| Product_D | Offer | 400 |
| Product_E | Offer | 600 |