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

EmailAddressSubscriberKeyFirstNameLookupKey
doug@limedash.com8473Doug1
suzy@limedash.com5497Suzy1
dale@limedash.com7114Dale1
barb@limedash.com5767Barb1
curt@limedash.com5152Curt1
nora@limedash.com7014Nora1
leon@limedash.com8225Leon1
lily@limedash.com9496Lily1

(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

How can leaders learn empathy?

[…] maximizing shareholder value […] where we prioritize the wants, needs, and desires of an external constituency over our own customers and our own employees. That’s like asking a coach who’s trying to build a good team what the fans want rather than what the players need. That’s the standard of business today. […] We need to reject that. […] It no longer works. […] We need to build empathetic leaders. We need to build leaders who understand that their responsibility is a human job. We don’t sit behind desks and lead a company from spreadsheets. We cannot. Because as we’ve seen it creates discontent inside our companies.