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

Marketing Cloud Engagement	✅ Yes
Marketing Cloud Next	❌ No

Syntax

1LookupOrderedRows(dataExt,
2                  numRows,
3                  sortColumn,
4                  searchColumn1, searchValue1,
5                  [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.

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.

1<table>
2  <tr>
3    <th>Rank</th>
4    <th>Name</th>
5    <th>Population</th>
6    <th>Area (km²)</th>
7  </tr>
8%%[
9  Var @territoriesByPopulation
10  Set @territoriesByPopulation = LookupOrderedRows("Administrative Divisions of Canada",
11                                                   /* Return all values */
12                                                   0,
13                                                   /* Value to sort by and sort order */
14                                                   /* Note that case doesn't match source data */
15                                                   "population2021 DESC",
16                                                   /* Retrieve data that matches this column and value */
17                                                   /* Note that case doesn't match source data */
18                                                   "type", "territory"
19                                                   )
20  Set @rowCount = RowCount(@territoriesByPopulation)
21
22  /* Only output content if the rowset contains data. */
23  If @rowCount > 0 then
24    /* Iterate through rowset. */
25    For @counter = 1 to @rowCount do
26      Var @row, @territoryName, @population, @area
27      Set @row = Row(@territoriesByPopulation, @counter)
28      Set @territoryName = Field(@row, "Name")
29      Set @population = Field(@row, "Population2021")
30      Set @area = Field(@row, "AreaKM2")
31]%%
32  <!-- Create a table row for each row in the rowset. -->
33  <tr>
34    <td>%%=v(@counter)=%%</td>
35    <td>%%=v(@territoryName)=%%</td>
36    <td>%%=v(@population)=%%</td>
37    <td>%%=v(@area)=%%</td>
38  </tr>
39%%[
40      Next @counter
41  EndIf
42]%%
43</table>

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

LookupOrderedRows()

Availability

Syntax

Usage

See Also

Contents