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-sensitive.

AMPscript includes several variations of the LookupOrderedRowsCS() function.

The LookupOrderedRowsCS() 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" (descending). This value is case-sensitive.
  • searchColumn1 (string): Required. The name of the column to search. This value is case-sensitive.
  • searchValue1 (string): Required. The value in the specified column that identifies the rows to retrieve. This value is case-sensitive.

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

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

BCBritish ColumbiaVictoriaVancouver5000879944735Province
NBNew BrunswickFrederictonMoncton77561072908Province
NLNewfoundland and LabradorSt. John'sSt. John's510550405212Province
NSNova ScotiaHalifaxHalifax96938355284Province
PEPrince Edward IslandCharlottetownCharlottetown1543315660Province
QCQuebecQuebec CityMontreal85018331542056Province
NTNorthwest TerritoriesYellowknifeYellowknife410701346106Territory

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.

The code outputs a table that contains this data.

RankNamePopulationArea (km²)
1Northwest Territories410701346106