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

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

AbbrevNameCapitalLargestCityPopulation2021AreaKM2Type
ABAlbertaEdmontonCalgary4262635661848Province
BCBritish ColumbiaVictoriaVancouver5000879944735Province
MBManitobaWinnipegWinnipeg1342153647797Province
NBNew BrunswickFrederictonMoncton77561072908Province
NLNewfoundland and LabradorSt. John'sSt. John's510550405212Province
NSNova ScotiaHalifaxHalifax96938355284Province
ONOntarioTorontoToronto142239421076395Province
PEPrince Edward IslandCharlottetownCharlottetown1543315660Province
QCQuebecQuebec CityMontreal85018331542056Province
SKSaskatchewanReginaSaskatoon1132505651036Province
NTNorthwest TerritoriesYellowknifeYellowknife410701346106Territory
YTYukonWhitehorseWhitehorse40232482443Territory
NUNunavutIqaluitIqaluit368582093190Territory

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
2Yukon40232482443
3Nunavut368582093190