sfmc.guide
Engagement Next
⌘K

ExecuteFilterOrderedRows

Executes a data-extension-based data filter and returns the matching rows sorted by a column and capped to a row count. Runtime-proven on a live Marketing Cloud Engagement CloudPage.

Runtime verified Test scripts included

Syntax

ExecuteFilterOrderedRows(dataFilterExternalId, numRows, sortColumn)  →  rowset
3 arguments — exactly

Parameters

Name Type Required Description
dataFilterExternalId string Yes External key of a data filter that is based on a data extension
numRows number Yes Maximum number of rows to return
sortColumn string Yes Sort expression as Column direction, e.g. Score desc

Example

%%[
  VAR @rows
  SET @rows = ExecuteFilterOrderedRows("AMP_VERIFY_FILTER", 1, "Score desc")
]%%
Top match: %%=v(Field(Row(@rows, 1), "Id"))=%%

With a filter FirstName Equals "Alice" over rows F1 (Score 10) and F3 (Score 30), renders Top match: F3 — the highest-scored Alice row, returned as the single row allowed by numRows.

Raise numRows and flip the direction to page a longer, ascending list:

%%[
  VAR @rows, @i, @r
  SET @rows = ExecuteFilterOrderedRows("AMP_VERIFY_FILTER", 10, "Score asc")
  FOR @i = 1 TO RowCount(@rows) DO
    SET @r = Row(@rows, @i)
]%%
%%=v(Field(@r, "Score"))=%%
%%[
  NEXT @i
]%%

Return value

rowset — the filtered rows, sorted by sortColumn and truncated to numRows. Pass it to RowCount, Row and Field. A filter that matches nothing returns an empty rowset.

Behaviour

Sorts and caps in one call. numRows limits the row count and sortColumn orders the result — with numRows 1 and Score desc, exactly the single top row was returned even though two rows matched the filter.

The sort argument is a Column direction string. Supply the column name followed by asc or desc.

Only data-extension-based filters. As with ExecuteFilter, the referenced filter must be built on a data extension, and the value comparison is case-insensitive.

Show test script
%%[
  VAR @b
  SET @b = RequestParameter("b")

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

  IF @b == "seed" THEN
    DeleteData("AMP_VERIFY_SCRATCH", "Id", "F1")
    DeleteData("AMP_VERIFY_SCRATCH", "Id", "F2")
    DeleteData("AMP_VERIFY_SCRATCH", "Id", "F3")
    InsertData("AMP_VERIFY_SCRATCH", "Id", "F1", "FirstName", "Alice", "Score", 10)
    InsertData("AMP_VERIFY_SCRATCH", "Id", "F2", "FirstName", "Bob", "Score", 20)
    InsertData("AMP_VERIFY_SCRATCH", "Id", "F3", "FirstName", "Alice", "Score", 30)
    OutputLine(Concat("seeded"))
  ENDIF

  IF @b == "run" THEN
    VAR @rs, @cnt, @i, @r
    SET @rs = ExecuteFilterOrderedRows("AMP_VERIFY_FILTER", 1, "Score desc")
    SET @cnt = RowCount(@rs)
    OutputLine(Concat("rowCount=[", @cnt, "]"))
    FOR @i = 1 TO @cnt DO
      SET @r = Row(@rs, @i)
      OutputLine(Concat("  ", Field(@r, "Id"), " FirstName=[", Field(@r, "FirstName"), "] Score=[", Field(@r, "Score"), "]"))
    NEXT @i
  ENDIF
]%%

Availability

Platform Available
Marketing Cloud Engagement Yes
Marketing Cloud Next No

See also