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.

AMPscript includes several variations of the LookupOrderedRowsCS() function.

• The LookupOrderedRows() function is a case-insensitive version of this function.
• The LookupRows() function returns unordered rows.
• The LookupRowsCS() function is a case-sensitive version of LookupRows().

1LookupOrderedRowsCS(dataExt,
2                    numRows,
3                    sortColumn,
4                    searchColumn1, searchValue1,
5                    [searchColumn2, searchValue2 ...])

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

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 = LookupOrderedRowsCS("Administrative Divisions of Canada",
11                                                   /* Return all values */
12                                                   0,
13                                                   /* Value to sort by and sort order */
14                                                   "Population2021 DESC",
15                                                   /* Retrieve data that matches this column and value */
16                                                   "Type", "Territory"
17                                                   )
18  Set @rowCount = RowCount(@territoriesByPopulation)
19
20  /* Only output content if the rowset contains data. */
21  If @rowCount > 0 then
22    /* Iterate through rowset. */
23    For @counter = 1 to @rowCount do
24      Var @row, @territoryName, @population, @area
25      Set @row = Row(@territoriesByPopulation, @counter)
26      Set @territoryName = Field(@row, "Name")
27      Set @population = Field(@row, "Population2021")
28      Set @area = Field(@row, "AreaKM2")
29]%%
30  <!-- Create a table row for each row in the rowset. -->
31  <tr>
32    <td>%%=v(@counter)=%%</td>
33    <td>%%=v(@territoryName)=%%</td>
34    <td>%%=v(@population)=%%</td>
35    <td>%%=v(@area)=%%</td>
36  </tr>
37%%[
38      Next @counter
39  EndIf
40]%%
41</table>

The code outputs a table that contains this data.

• LookupOrderedRows()