LookupOrderedRows

by | Apr 21, 2018 | Book |

LookupOrderedRows

This function returns a set of ordered rows from a Data Extension.

Arguments

LookupOrderedRows(1,2,3,4,5,[6a,6b]...)

Ordinal Type Required Description
1 String True Name of the Data Extension from which to return rows
2 Number True Number of rows to return. Specifying 0 or -1 returns all matching rows. The maximum number of rows returned is 2,000.
3 String True Order-by clause that determines the order of the rows returned. Include a suffix of ASC (default) or DESC to specify the sort order of the rows. Specify multiple sort columns by separating them with a comma.
4 String True Name of the column that identifies the rows to retrieve
5 String True Value that identifies the rows to retrieve
6a String False Additional column name that identifies the rows to retrieve (see note)
6b String False Additional value that identifies the rows to retrieve (see note)

NOTE: Additional pairs of columns and values can be appended as arguments.

Example 1

The following illustrates the use of this function with the minimum number of arguments.

Data Extension: LoyaltyMembers

Name Data Type Length Primary Key Nullable Default Value
EmailAddress EmailAddress 254 N N
SubscriberKey Text 254 Y N
FirstName Text 50 N Y
Region Text 50 N Y
Rank Number N Y

The LoyaltyMembers Data Extension includes these rows:

EmailAddress SubscriberKey FirstName Region Rank
doug@limedash.com 8473 Doug north 1
suzy@limedash.com 5497 Suzy North 3
dale@limedash.com 7114 Dale West 2
barb@limedash.com 5767 Barb North 1
curt@limedash.com 5152 Curt north 2
nora@limedash.com 7014 Nora South 1
leon@limedash.com 8225 Leon East 3
lily@limedash.com 9496 Lily East 1

Here is an example utilizing the preceding context:

%%[
var @rows, @row, @rowCount, @numRowsToReturn, @region, @i

set @region = AttributeValue("Region") /* value from attribute or DE column in send context */
set @region = "North" /* or a literal value */
set @numRowsToReturn = 0 /* 0 means all, max 2000 */
set @rows = LookupOrderedRows("LoyaltyMembers", @numRowsToReturn, "rank desc, firstName asc", "region", @region)
set @rowCount = rowcount(@rows)

output(concat("region: ", @region))

if @rowCount > 0 then

  for @i = 1 to @rowCount do

    var @firstName, @emailAddress, @rank
    set @row = row(@rows, @i) /* get row based on counter */
    set @rank = field(@row,"rank")
    set @firstName = field(@row,"firstName")
    set @emailAddress = field(@row,"emailAddress")

    ]%%

    <br>Row %%=v(@i)=%%, rank: %%=v(@rank)=%%, firstName: %%=v(@firstName)=%%, emailAddress: %%=v(@emailAddress)=%%

    %%[

  next @i ]%%

%%[ else ]%%

No rows found

%%[ endif ]%%

Output

region: North
Row 1, rank: 3, firstName: Suzy, emailAddress: suzy@limedash.com
Row 2, rank: 2, firstName: Curt, emailAddress: curt@limedash.com
Row 3, rank: 1, firstName: Barb, emailAddress: barb@limedash.com
Row 4, rank: 1, firstName: Doug, emailAddress: doug@limedash.com

Example 2


Not a subscriber? Subscribe now.