Send one email per unique data extension field value without de-duplicating emails

Question

I'm trying to send one email per order number to a data extension. Data extension is set up so contact id relates to subscribers on subscriber key. No primary key. The data would look something like this:

Contact ID   Name   Order Number   Item Number   Email
123          John   001            1             [email protected]
123          John   002            2             [email protected]
321          Jane   003            3             [email protected]
321          Jane   003            4             [email protected]

I'm using this data to personalize the email with an order summary with AMPscript similar to this solution. As you can see, there will be multiples of the same contact, sometimes multiple items on the same order number. What I want is to send one email per order number. Right now, when I send to this data extension, a contact will receive one email per record, so contact 321 receives two emails, even though it's just one order number. I don't want to de-duplicate either, because then contact 123 wouldn't receive two emails for his two different order numbers. Is it possible to achieve what I want here? Is there a solution in email send settings or in data extension settings?

Answer 1

You could deduplicate based on order_number rather than Contact ID using below:

SELECT
CONTACT_ID,ORDER_NUMBER,EMAIL,ITEM_NUMBER,NAME
FROM
(SELECT CONTACT_ID,ORDER_NUMBER,EMAIL,ITEM_NUMBER,NAME,
ROW_NUMBER() OVER (PARTITION BY ORDER_NUMBER ORDER BY ITEM_NUMBER ASC)
AS Row
FROM "ORDER"
) as s
WHERE row=1

Considerations:

1. Timeout: If your data is very large.
2. This will pick up the first record as per item number, is there any business requirement on which record should be selected in case of same order number?