LookupOrderedRowsCS()
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. The LookupOrderedRows() function is a case-insensitive version of this 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 for rows to retrieve. This value is case-sensitive.searchValue1
(string): Required. The value 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.
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 |