sfmc.guide
Engagement Next
⌘K

LookupOrderedRows

Returns a sorted, row-limited rowset of matching rows from a data extension. Runtime-proven on a live Marketing Cloud Engagement CloudPage — including the sort direction and the row limit.

Runtime verified Test scripts included

Syntax

LookupOrderedRows(dataExt, numRows, sortColumn, searchColumn1, searchValue1[, searchColumnN, searchValueN, ...])  →  rowset

Parameters

Name Type Required Description
dataExt string Yes Name or external key of the data extension to read
numRows number Yes Maximum rows to return; a value below 1 returns all matches (up to 2,000)
sortColumn string Yes Column to sort by, optionally followed by ASC or DESC
searchColumn1 string Yes First column to filter on
searchValue1 string | number Yes Value the first column must equal
searchColumnN string No Further filter columns, each paired with a value
searchValueN string | number No Value the corresponding further column must equal

Example

%%[
  VAR @rows
  SET @rows = LookupOrderedRows("AMP_VERIFY_SCRATCH", 2, "Score ASC", "FirstName", "Alice")
]%%
Top Id: %%=v(Field(Row(@rows, 1), "Id"))=%% Score: %%=v(Field(Row(@rows, 1), "Score"))=%%

With Alice rows scoring 10, 20 and 30, renders Top Id: O1 Score: 10 — the lowest score first.

Flip the direction and cap the count to page results, newest first:

%%[
  VAR @recent
  SET @recent = LookupOrderedRows("AMP_VERIFY_SCRATCH", 5, "Score DESC", "FirstName", "Alice")
]%%
Highest score: %%=v(Field(Row(@recent, 1), "Score"))=%%

Renders Highest score: 30.

Return value

rowset — matching rows sorted by sortColumn and capped at numRows, read 1-based with Row and Field.

A no-match returns an empty rowset; guard with RowCount.

Behaviour

Sorts by the named column in the requested direction. Score ASC returned the score-10 row first, then score-20; Score DESC returned the score-30 row first.

numRows caps the returned count. With three matching rows, numRows of 1 returned a single row.

Matching on the search criteria is case-insensitive. A search for Alice also returned the row whose FirstName was lowercase alice. Use LookupOrderedRowsCS for a case-sensitive comparison.

A no-match returns an empty rowset. RowCount on it is 0; the page does not abort.

Show test script
%%[
  VAR @b, @rs, @rc, @i
  SET @b = RequestParameter("b")

  IF @b == "zzz" THEN
    OutputLine(Concat("CTRL=[alive]"))
  ENDIF

  IF @b == "safe" THEN
    DeleteData("AMP_VERIFY_SCRATCH", "Id", "O1")
    DeleteData("AMP_VERIFY_SCRATCH", "Id", "O2")
    DeleteData("AMP_VERIFY_SCRATCH", "Id", "O3")
    InsertData("AMP_VERIFY_SCRATCH", "Id", "O1", "FirstName", "Alice", "Score", 10)
    InsertData("AMP_VERIFY_SCRATCH", "Id", "O2", "FirstName", "Alice", "Score", 30)
    InsertData("AMP_VERIFY_SCRATCH", "Id", "O3", "FirstName", "alice", "Score", 20)

    /* top 2 by Score ASC */
    SET @rs = LookupOrderedRows("AMP_VERIFY_SCRATCH", 2, "Score ASC", "FirstName", "Alice")
    SET @rc = RowCount(@rs)
    OutputLine(Concat("OrderedRows(2,Score ASC) count=[", @rc, "]"))
    FOR @i = 1 TO @rc DO
      OutputLine(Concat("  ", @i, " Id=[", Field(Row(@rs, @i), "Id"), "] Score=[", Field(Row(@rs, @i), "Score"), "]"))
    NEXT @i

    /* DESC direction */
    OutputLine(Concat("OrderedRows(5,Score DESC) firstScore=[", Field(Row(LookupOrderedRows("AMP_VERIFY_SCRATCH", 5, "Score DESC", "FirstName", "Alice"), 1), "Score"), "]"))
    /* numRows limit */
    OutputLine(Concat("OrderedRows(1,Score ASC) count=[", RowCount(LookupOrderedRows("AMP_VERIFY_SCRATCH", 1, "Score ASC", "FirstName", "Alice")), "]"))
  ENDIF
]%%

Availability

Platform Available
Marketing Cloud Engagement Yes
Marketing Cloud Next Check the official reference

See also