SORT command

Concept Information

Sorting records

Sorts records in an Analytics table into an ascending or descending sequential order, based on a specified key field or fields. The results are output to a new, physically reordered Analytics table.

Syntax

SORT {<ON> key_field <D> <...n>|<ON> ALL <EXCLUDE field_name <...n>>} <FIELDS field_name <AS display_name> <...n>|FIELDS ALL <EXCLUDE field_name <...n>>> TO tablename <LOCAL> <IF test> <WHILE test> <FIRST range|NEXT range> <APPEND> <OPEN> <ISOLOCALE locale_code>

Parameters

Name Description
ON key_field D <...n> | ON ALL

The key field or fields, or the expression, to use for sorting.

You can sort by any type of field, including computed fields and ad hoc expressions, regardless of data type.

  • ON key_field use the specified field or fields

    If you sort by more than one field, you create a nested sort in the output table. The order of nesting follows the order in which you specify the fields.

    Include D to sort a key field in descending order. The default sort order is ascending.

  • ON ALL use all fields in the table

    If you sort by all the fields in a table you create a nested sort in the output table. The order of nesting follows the order in which the fields appear in the table layout.

    An ascending sort order is the only option for ON ALL.
EXCLUDE field_name

optional

Only valid when sorting using ON ALL.

The field or fields to exclude from the command. EXCLUDE allows you to fine-tune ON ALL, by excluding the specified fields.

EXCLUDE must immediately follow ON ALL. For example:

ON ALL EXCLUDE field_1 field_2

FIELDS field_name <...n> | FIELDS ALL

optional

Note

Key fields are automatically included in the output table, and do not need to be specified using FIELDS.

The fields to include in the output:

  • FIELDS field_name use the specified fields

    Fields are used in the order that you list them.

    Converts computed fields to physical fields of the appropriate data type in the destination table – ASCII or Unicode (depending on the edition of Analytics), ACL (the native numeric data type), Datetime, or Logical. Populates the physical fields with the actual computed values.

  • FIELDS ALL use all fields in the table

    Fields are used in the order that they appear in the table layout.

    Converts computed fields to physical fields of the appropriate data type in the destination table – ASCII or Unicode (depending on the edition of Analytics), ACL (the native numeric data type), Datetime, or Logical. Populates the physical fields with the actual computed values.

  • omit FIELDS the entire record is included in the sorted output table: all fields, and any undefined portions of the record

    Fields are used in the order that they appear in the table layout.

    Computed fields are preserved.

Tip

If you need only a portion of the data contained in a record, do not include all fields, or the entire record, in the sorted output table. Select only the fields you need, which in most cases speeds up the sorting process.

AS display_name

optional

Only valid when using FIELDS.

The display name (alternate column title) for the field in the view in the new Analytics table. If you want the display name to be the same as the field name, or an existing display name in the source table, do not use AS.

Specify display_name as a quoted string. Use a semi-colon (;) between words if you want a line break in the column title.

Note

AS works only when outputting to a new table. If you are appending to an existing table, the alternate column titles in the existing table take precedence.
EXCLUDE field_name

optional

Only valid when using FIELDS ALL.

The field or fields to exclude from the command. EXCLUDE allows you to fine-tune FIELDS ALL, by excluding the specified fields.

EXCLUDE must immediately follow FIELDS ALL. For example:

FIELDS ALL EXCLUDE field_1 field_2
TO table_name

The location to send the results of the command to:

  • table_name saves the results to an Analytics table

    Specify table_name as a quoted string with a .FIL file extension. For example: TO "Output.FIL"

    By default, the table data file (.FIL) is saved to the folder containing the Analytics project.

    Use either an absolute or relative file path to save the data file to a different, existing folder:

    • TO "C:\Output.FIL"
    • TO "Results\Output.FIL"

    Note

    Table names are limited to 64 alphanumeric characters, not including the .FIL extension. The name can include the underscore character ( _ ), but no other special characters, or any spaces. The name cannot start with a number.
LOCAL

optional

Saves the output file in the same location as the Analytics project.

Note

Applicable only when running the command against a server table with an output file that is an Analytics table.

The LOCAL parameter must immediately follow the TO parameter.
IF test

optional

A conditional expression that must be true in order to process each record. The command is executed on only those records that satisfy the condition.

Note

The IF parameter is evaluated against only the records remaining in a table after any scope parameters have been applied (WHILE, FIRST, NEXT).
WHILE test

optional

A conditional expression that must be true in order to process each record. The command is executed until the condition evaluates as false, or the end of the table is reached.

Note

If you use WHILE in conjunction with FIRST or NEXT, record processing stops as soon as one limit is reached.
FIRST range | NEXT range

optional

The number of records to process:

  • FIRST start processing from the first record until the specified number of records is reached
  • NEXT start processing from the currently selected record until the specified number of records is reached

Use range to specify the number of records to process.

If you omit FIRST and NEXT, all records are processed by default.

APPEND

optional

Appends the command output to the end of an existing file instead of overwriting it.

Note

You must ensure that the structure of the command output and the existing file are identical:

  • the same fields
  • the same field order
  • matching fields are the same length
  • matching fields are the same data type

Analytics appends output to an existing file regardless of its structure. If the structure of the output and the existing file do not match, jumbled, missing, or inaccurate data can result.

OPEN

optional

Open the table and apply the index to the table.

ISOLOCALE locale_code

optional

Note

Applicable in the Unicode edition of Analytics only.

The system locale in the format language_country. For example, to use Canadian French, enter fr_ca.

Use the following codes:

  • language ISO 639 standard language code
  • country ISO 3166 standard country code

    If you do not specify a country code, the default country for the language is used.

If you do not use ISOLOCALE, the default system locale is used.

Examples

Sort on a single field, output entire records

You want to sort the records in the sample Inventory table by product number. The sorted records are extracted to a new Analytics table called Inventory_Product_Number.

Entire records are included in the output table:

SORT ON ProdNo TO "Inventory_Product_Number"

To switch from the default ascending sort order to a descending sort order, you add D after the key field name:

SORT ON ProdNo D TO "Inventory_Product_Number"

Sort on a single field, output a subset of fields

You want to sort the records in the sample Inventory table by product number. Only the key field and the specified non-key fields are extracted to a new Analytics table called Inventory_Quantity_on_Hand.

The third non-key field, QtyOH, is given the display name Qty on Hand in the output table:

SORT ON ProdNo FIELDS ProdDesc ProdStat QtyOH AS "Qty on Hand" TO "Inventory_Quantity_on_Hand"

Sort on a single field, output all fields

You want to sort the records in the sample Inventory table by product number. All fields are extracted to a new Analytics table called Inventory_Product_Number.

The difference between using FIELDS ALL and outputting the entire record is that FIELDS ALL converts any computed fields in the source table to physical fields in the output table, and populates the fields with the actual computed values:

SORT ON ProdNo FIELDS ALL TO "Inventory_Product_Number"

Sort on multiple fields (nested sort)

You want to sort the records in the sample Inventory table by location, then by product class, and then by product number. The sorted records are extracted to a new Analytics table called Inventory_Location_Class_Number.

SORT ON Location ProdCls ProdNo TO "Inventory_Location_Class_Number"

Sort using related fields

You want to sort the records in the sample Ap_Trans table by the following fields:

  • vendor state (related Vendor table)
  • vendor city (related Vendor table)
  • vendor number (Ap_Trans table)

All three key fields and the specified non-key fields, including the related field Vendor.Vendor_Name, are extracted to a new Analytics table called Ap_Trans_State_City:

SORT ON Vendor.Vendor_State Vendor.Vendor_City Vendor_No FIELDS Vendor.Vendor_Name Invoice_No Invoice_Date Invoice_Amount Prodno Quantity Unit_Cost TO "Ap_Trans_State_City"

Remarks

For more information about how this command works, see Sorting records.

The sort sequence used by the SORT command

The SORT command uses whatever sort sequence is specified in the Sort Order option (Tools > Options > Table). The default sort sequences are shown below.

For detailed information, see The Sort Order option and sort sequences .

Analytics Edition

Sort Order default

Associated sort sequence

non-Unicode

System Default

(ASCII)

Numbers, then uppercase, then lowercase:

0, 1, 2... A, B, C... a, b, c...

For example, “Z” sorts before “a”.

Unicode

Mix Languages (UCA)

(Unicode collation algorithm)

Numbers, then lowercase and uppercase intermixed:

0, 1, 2... a, A, b, B, c, C...

For example, “a” sorts before “Z”.

Case sensitivity

SORT is case sensitive. Depending on which edition of Analytics you are using (non-Unicode or Unicode), casing in strings may affect sorting.

You can use the UPPER( ) function in conjunction with SORT if you do not want case to affect sorting:

SORT ON UPPER(key_field) TO "Sorted_Table"

Sorting on related fields

You can sort on related fields, and include related fields as non-key fields in a sorted output table. To reference a related field in the SORT command specify child table name.field name.

Fixed-length vs variable-length data files

The SORT command works on both fixed-length and variable-length data files.