Sorting Multi-valued Columns

If the column being sorted is multi-valued, the By keyword will cause an "exploding sort." In an exploding sort, each individual value in a multi-valued column is treated as if it were in a row by itself. The result is a list or report with an entry for each value in the multi-valued column of each row being processed.

Lists or reports created with an exploding sort will have a two-part key for each row being processed. The first part of the key will be the row's original key. The second part of the key, separated from the first part by a value mark, will be the value number within the multi-valued column of the sort data.

For example, a CUSTOMER row might have several contact names. If a report were created that sorted by contact name, a portion of the report might look like this:

CUST. NO.    COMPANY    CONTACT
---------    -------    --------
C100         ACME       DAPHNE     
C100         ACME       ROXANNE    
C100         ACME       SABRINA    
C100         ACME       ZACHARY

Here, the original customer row with four contact names has been "exploded" into four entries on the report

This command lists rows from the CUSTOMERS table sorted into state order.

List CUSTOMERS By ST

This command creates an active list of keys out of rows in the CUSTOMERS table. The list is sorted by state and by ZIP code. Within each ZIP code, the list is sorted by COMPANY.

Select CUSTOMERS By ST By ZIP By COMPANY

This command creates from the CUSTOMERS table an active list of keys sorted by invoice amount, with the largest invoice amount at the top of the list. Within each like invoice amount, the list is sorted by key.

Select CUSTOMERS By-DSND INVOICE_AMT