sfmc.guide
Engagement Next
⌘K

LookupOrderedRowsCS

Returns a sorted, row-limited rowset of matching rows using a case-sensitive comparison. Runtime-proven on a live Marketing Cloud Engagement CloudPage — including the difference from the case-insensitive LookupOrderedRows.

Runtime verified Test scripts included

Syntax

LookupOrderedRowsCS(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, compared case-sensitively
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 = LookupOrderedRowsCS("AMP_VERIFY_SCRATCH", 5, "Score ASC", "FirstName", "Alice")
]%%
Matches: %%=v(RowCount(@rows))=%% first Id: %%=v(Field(Row(@rows, 1), "Id"))=%%

With rows Alice, Alice and lowercase alice, renders Matches: 2 first Id: O1 — the lowercase row is excluded and the remaining two are ordered by score.

The contrast with the case-insensitive LookupOrderedRows on the same data:

%%[
  VAR @cs, @ci
  SET @cs = RowCount(LookupOrderedRowsCS("AMP_VERIFY_SCRATCH", 5, "Score ASC", "FirstName", "Alice"))
  SET @ci = RowCount(LookupOrderedRows("AMP_VERIFY_SCRATCH", 5, "Score ASC", "FirstName", "Alice"))
]%%
CS: %%=v(@cs)=%% CI: %%=v(@ci)=%%

Renders CS: 2 CI: 3.

Return value

rowset — matching rows selected case-sensitively, 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

Case-sensitive matching combined with ordering and a row limit. It is LookupOrderedRows with the case-sensitive comparison of LookupRowsCS. On a fixture with Alice, Alice and alice, a search for Alice returned 2 rows (ordered by score) and excluded the lowercase row that the case-insensitive form included.

numRows, sortColumn and the variadic criteria behave as in LookupOrderedRows. Only the comparison of the search values differs.

Show test script
%%[
  VAR @b, @cs, @ci
  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)

    /* case-sensitive, ordered ascending, top 5 */
    SET @cs = LookupOrderedRowsCS("AMP_VERIFY_SCRATCH", 5, "Score ASC", "FirstName", "Alice")
    OutputLine(Concat("OrderedRowsCS(Alice) count=[", RowCount(@cs), "] firstId=[", Field(Row(@cs, 1), "Id"), "]"))

    /* case-insensitive counterpart includes the lowercase row */
    SET @ci = LookupOrderedRows("AMP_VERIFY_SCRATCH", 5, "Score ASC", "FirstName", "Alice")
    OutputLine(Concat("OrderedRows(Alice) count=[", RowCount(@ci), "]"))
  ENDIF
]%%

Availability

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

See also