LookupOrderedRows()

Returns rows from a data extension sorted in ascending or descending order based on a column that you specify. If the function doesn’t find the search values that you specify, it returns an empty rowset. This function is case-insensitive.

AMPscript includes several variations of the LookupOrderedRows() function.

• The LookupOrderedRowsCS() function is a case-sensitive version of this function.
• The LookupRows() function returns unordered rows.
• The LookupRowsCS() function is a case-sensitive version of LookupRows().

Availability

Syntax

LookupOrderedRows(dataExt,
                  numRows,
                  sortColumn,
                  searchColumn1, searchValue1,
                  [searchColumn2, searchValue2 ...])

The LookupOrderedRows() function has five parameters.

• dataExt (string): Required. The name of the data extension that contains the data that you want to retrieve.
• numRows (number): Required. The number of rows to return. If you specify a value less than 1, the function returns all rows, up to a maximum of 2,000 rows.
• sortColumn (string): Required. The column to sort data by, followed by a space and either ASC for ascending order or DESC for descending. This value is case-insensitive. Specify multiple columns by separating them with a comma. For example, "LastName ASC, FirstName ASC".
• searchColumn1 (string): Required. The name of the column to search. This value is case-insensitive.
• searchValue1 (string): Required. The value in the specified column that identifies the rows to retrieve. This value is case-insensitive.

You can optionally append additional search columns and values to the end of the parameter string.

Usage

This example uses a data extension called "Administrative Divisions of Canada," which contains the data in this table.

This code retrieves a complete list of regions where the value of the Type column is Territory and outputs the results in descending order based on the values in the Population2021 column.

<table>
  <tr>
    <th>Rank</th>
    <th>Name</th>
    <th>Population</th>
    <th>Area (km²)</th>
  </tr>
%%[
  Var @territoriesByPopulation
  Set @territoriesByPopulation = LookupOrderedRows("Administrative Divisions of Canada",
                                                   /* Return all values */
                                                   0,
                                                   /* Value to sort by and sort order */
                                                   /* Note that case doesn't match source data */
                                                   "population2021 DESC",
                                                   /* Retrieve data that matches this column and value */
                                                   /* Note that case doesn't match source data */
                                                   "type", "territory"
                                                   )
  Set @rowCount = RowCount(@territoriesByPopulation)

  /* Only output content if the rowset contains data. */
  If @rowCount > 0 then
    /* Iterate through rowset. */
    For @counter = 1 to @rowCount do
      Var @row, @territoryName, @population, @area
      Set @row = Row(@territoriesByPopulation, @counter)
      Set @territoryName = Field(@row, "Name")
      Set @population = Field(@row, "Population2021")
      Set @area = Field(@row, "AreaKM2")
]%%
  <!-- Create a table row for each row in the rowset. -->
  <tr>
    <td>%%=v(@counter)=%%</td>
    <td>%%=v(@territoryName)=%%</td>
    <td>%%=v(@population)=%%</td>
    <td>%%=v(@area)=%%</td>
  </tr>
%%[
      Next @counter
  EndIf
]%%
</table>

The code outputs a table that contains this data.

See Also

• LookupOrderedRowsCS()