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?

1 Answer 1


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

AS Row
) as s
WHERE row=1


  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?
  • Hi, thanks for the quick response. This is probably an obvious question, but is this AMPscript that goes in the email itself? And on your consideration 2, the system would pick the record with item id 3 for contact 321, since that's the first item id for that same order number 003 -- am I understanding that right?
    – kimchi
    Oct 20, 2020 at 16:36
  • On closer look, is this an SQL query? Apologies, I wasn't familiar enough to recognize it. If there is possibly any solution that doesn't include it, that would be excellent, but I'm guessing there's not a great workaround without queries.
    – kimchi
    Oct 20, 2020 at 16:52
  • Yes it is a SQL Query, looking at your problem statement, this was the best solution I could think of :) Oct 21, 2020 at 7:01

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .