By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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 p.*
from (select p.*,
row_number() over (partition by product order by field(Event_Type, 'Order', 'Offer')) as seqnum
from Purchasing p
) p
where seqnum = 1
| Event_Type | Product | Quantity | seqnum |
|---|---|---|---|
| Offer | Product_A | 300 | 1 |
| Order | Product_B | 250 | 1 |
| Order | Product_C | 450 | 1 |
| Offer | Product_D | 400 | 1 |
| Offer | Product_E | 600 | 1 |
select p.*
from Purchasing p
where p.event_type = 'Order' or
(p.event_type = 'Offer' and
not exists (select 1 from Purchasing p2 where p2.product = p.product and p2.event_type = 'Order')
)
order by product
| Event_Type | Product | Quantity |
|---|---|---|
| Offer | Product_A | 300 |
| Order | Product_B | 250 |
| Order | Product_C | 450 |
| Offer | Product_D | 400 |
| Offer | Product_E | 600 |