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.
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
[email protected] 8473 Doug north 1
[email protected] 5497 Suzy North 3
[email protected] 7114 Dale West 2
[email protected] 5767 Barb North 1
[email protected] 5152 Curt north 2
[email protected] 7014 Nora South 1
[email protected] 8225 Leon East 3
[email protected] 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: [email protected]
Row 2, rank: 2, firstName: Curt, emailAddress: [email protected]
Row 3, rank: 1, firstName: Doug, emailAddress: [email protected]
Row 4, rank: 1, firstName: Barb, emailAddress: [email protected]

Example 2


Not a subscriber? Subscribe now.