About sorting

You can sort records into an ascending or descending sequential order and output the results to a new, physically reordered ACL table. Outputting to an ACL table and including all fields from the active table – even if you sort on only one field – is the only output option. You can sort any type of field, including computed fields and ad hoc expressions, regardless of data type. Sorting on logical fields requires that Include Filters in Field Lists is selected (Tools > Options > Interface). Sorting works on both fixed-length and variable-length data files.

You can sort records using one key field, or you can create nested sorting schemes by sorting on multiple key fields (primary key field, secondary key field, and so on). Nested sorting supports mixing ascending and descending order, and mixing data types, across key fields. For example, a transaction table could be sorted in ascending order on a key date field, and within each day in descending order on a key amount field.

Table 1. Nested sorting with mixed ascending and descending order

Date field

(ascending)

Amount field

(descending, nested)

15 Jan 2011

$2300.00

15 Jan 2011

$1200.00

15 Jan 2011

 $600.00

16 Jan 2011

 $900.00

16 Jan 2011

 $100.00

17 Jan 2011

$4700.00

17 Jan 2011

 $900.00

17 Jan 2011

 $500.00

Sorting records is a prerequisite for joining or merging tables, and testing for duplicates and gaps. All these operations include the Presort option, which allows you to incorporate a preliminary sequential sorting of records when performing the operation. However, if you are performing multiple operations on a table, explicitly sorting the table first, rather than repeatedly using Presort, may be more efficient, especially if the table contains a large number of records.

Sorting a table requires a significant amount of system resources and may be slowed down if you are performing other tasks simultaneously. To optimize sorting performance, you can allocate a specific amount of memory for sorting to a maximum of 2000 MB (Tools > Options > Table > Sort Memory, or the SET SORTMEMORY command). You also need free disk space at least 2.5 times the size of the file being sorted for the creation of a temporary file used during the sorting process.

If you are sorting and outputting all the records in a table, you can set a control total on a numeric field to verify that all the records are in fact output to the new table. You set a control total for a field in the Table Layout dialog box. Once you have sorted and output the records, in the new table select Tools > Table History to compare the input and output control totals. For more information, see Defining physical data fields.

Indexing records is an alternative to sorting them, and in some situations may be a better choice. For more information, see Sorting versus indexing.

Related concepts
Testing sequential order, sorting, and indexing
About testing sequential order
About the Sort Order option and sort sequences
Sorting versus indexing
About indexing
Sorting and indexing using computed fields
About key fields
Related tasks
Sorting records
Sorting or indexing records using computed fields


(C) 2015 ACL Services Ltd. All Rights Reserved.