Selecting random data extension rows with AMPscript

In a triggered send scenario, there’s no chance to leverage query activities to prepare auxiliary data for display in an email. I recently had this request from a client:

Display a random set 6 of the products in a specific data extension in the body of this email.

That’s tricky with AMPscript since you can only retrieve ordered rows based on fields and values already in the data extension (e.g you can’t order by GUID(), random() or _customObjectKey with the lookup functions). There are also no arrays or sorting outside of the rowset lookup functions in AMPscript.

Here’s a solution that worked for me:

DataExtensionTest

(NOTE: I added a lookupKey column to my data extension with a default value to facilitate retrieving all of the rows)

Output 1


arr(1): {2}{6}{7}{4}{8}{3}{5}{1}
arr(2): 5497|7014|8225|5767|9496|7114|5152|8473
subscriberkey1: 5497, firstName: Suzy
subscriberkey2: 7014, firstName: Nora
subscriberkey3: 8225, firstName: Leon
subscriberkey4: 5767, firstName: Barb
subscriberkey5: 9496, firstName: Lily
subscriberkey6: 7114, firstName: Dale
subscriberkey7: 5152, firstName: Curt
subscriberkey8: 8473, firstName: Doug


Output2


arr(1): {7}{3}{5}{6}{8}{1}{2}{4}
arr(2): 8225|7114|5152|7014|9496|8473|5497|5767
subscriberkey1: 8225, firstName: Leon
subscriberkey2: 7114, firstName: Dale
subscriberkey3: 5152, firstName: Curt
subscriberkey4: 7014, firstName: Nora
subscriberkey5: 9496, firstName: Lily
subscriberkey6: 8473, firstName: Doug
subscriberkey7: 5497, firstName: Suzy
subscriberkey8: 5767, firstName: Barb


I’m sure there are other ways of approaching this. Know of a simpler way or an alternate approach? Please share!

Reference

• lookupRows
• multiply
• random
• replace
• buildRowSetFromString