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()
.
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.
Abbrev | Name | Capital | LargestCity | Population2021 | AreaKM2 | Type |
---|---|---|---|---|---|---|
AB | Alberta | Edmonton | Calgary | 4262635 | 661848 | Province |
BC | British Columbia | Victoria | Vancouver | 5000879 | 944735 | Province |
MB | Manitoba | Winnipeg | Winnipeg | 1342153 | 647797 | Province |
NB | New Brunswick | Fredericton | Moncton | 775610 | 72908 | Province |
NL | Newfoundland and Labrador | St. John's | St. John's | 510550 | 405212 | Province |
NS | Nova Scotia | Halifax | Halifax | 969383 | 55284 | Province |
ON | Ontario | Toronto | Toronto | 14223942 | 1076395 | Province |
PE | Prince Edward Island | Charlottetown | Charlottetown | 154331 | 5660 | Province |
QC | Quebec | Quebec City | Montreal | 8501833 | 1542056 | Province |
SK | Saskatchewan | Regina | Saskatoon | 1132505 | 651036 | Province |
NT | Northwest Territories | Yellowknife | Yellowknife | 41070 | 1346106 | Territory |
YT | Yukon | Whitehorse | Whitehorse | 40232 | 482443 | Territory |
NU | Nunavut | Iqaluit | Iqaluit | 36858 | 2093190 | Territory |
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.
Rank | Name | Population | Area (km²) |
---|---|---|---|
1 | Northwest Territories | 41070 | 1346106 |
2 | Yukon | 40232 | 482443 |
3 | Nunavut | 36858 | 2093190 |