Share via

SORT Command

  • Article

Sorts records in the currently selected table and outputs the sorted records to a new table.

SORT TO TableName ON FieldName1 [/A | /D] [/C]
   [, FieldName2 [/A | /D] [/C] ...]   [ASCENDING | DESCENDING]
   [Scope] [FOR lExpression1] [WHILE lExpression2]
   [FIELDS FieldNameList   | FIELDS LIKE Skeleton
   | FIELDS EXCEPT Skeleton]   [NOOPTIMIZE]

Parameters

  • TableName
    Specifies the name of the new table containing the sorted records. Visual FoxPro assumes a .dbf file name extension for tables. A .dbf extension is automatically assigned if the file name you include doesn't have an extension.

  • ON FieldName1
    Specifies the field in the currently selected table on which the sort is based. The contents and data type of the field determine the order of the records in the new table. By default, the sort is done in ascending order. You can't sort on memo or general fields.

    The following example sorts a table on the cust_id field. The customer table is opened and sorted, creating a new table named temp. The records in temp are ordered by the cust_id field.

    CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'data\testdata')
USE customer  && Opens Customer table
CLEAR
LIST FIELDS company, cust_id NEXT 3
SORT TO temp ON cust_id
USE temp
LIST FIELDS company, cust_id NEXT 3
WAIT WINDOW 'Now sorted on CUST_ID' NOWAIT

    You can include additional field names (FieldName2, FieldName3) to further order the new table. The first field FieldName1 is the primary sort field, the second field FieldName2 is the secondary sort field, and so on.

  • [/A | /D] [/C]
    For each field you include in the sort, you can specify an ascending or descending sort order. /A specifies an ascending order for the field. /D specifies a descending order. /A or /D can be included with any type of field.

    By default, the field sort order for character fields is case sensitive. If you include the /C option after the name of a character field, case is ignored. You can combine the /C option with the /A or /D option. For example, /AC or /DC.

    In the following example, a new table named clients is created. The orders table is sorted on the order_date field in ascending order and the freight field in descending order.

    USE orders
SORT TO clients ON order_date/A,freight/D

  • ASCENDING
    Specifies an ascending order for all fields not followed by /D.

  • DESCENDING
    Specifies a descending order for all fields not followed by /A.

    If you omit either ASCENDING or DESCENDING, the sort order is ascending by default.

  • Scope
    Specifies a range of records to sort. The scope clauses are: ALL, NEXT nRecords, RECORD nRecordNumber, and REST.

    The default scope for SORT is ALL records.

  • FOR lExpression1
    Specifies that only the records in the current table for which the logical condition lExpression1 evaluates to true (.T.) are included in the sort. Including FOR lets you conditionally sort records, filtering out undesired records.

    Rushmore Query Optimization optimizes a SORT ... FOR command if lExpression1 is an optimizable expression. For best performance, use an optimizable expression in the FOR clause.

    A discussion of expressions that Rushmore can optimize appears in Optimizing Applications.

  • WHILE lExpression2
    Specifies a condition whereby records from the current table are included in the sort for as long as the logical expression lExpression2 evaluates to true (.T.).

  • FIELDS FieldNameList
    Specifies fields from the original table to include in the new table that SORT creates. If you omit the FIELDS clause, all fields from the original table are included in the new table.

  • FIELDS LIKE Skeleton
    Specifies that fields from the original table that match the field skeleton Skeleton are included in the new table that SORT creates.

  • FIELDS EXCEPT Skeleton
    Specifies that all fields except those that match the field skeleton Skeleton are included in the new table that SORT creates.

    The field skeleton Skeleton supports wildcards. For example, to specify that all fields that begin with the letters A and P are included in the new table, use the following:

    SORT TO mytable ON myfield FIELDS LIKE A*,P*

    The LIKE clause can be combined with the EXCEPT clause:

    SORT TO mytable ON myfield FIELDS LIKE A*,P* EXCEPT PARTNO*

  • NOOPTIMIZE
    Disables Rushmore optimization of SORT.

    For more information, see SET OPTIMIZE and Using Rushmore Query Optimization to Speed Data Access.

Remarks

One or more specified fields in the current table determine the order in which the records appear in the new table.

Caution   Be sure you have enough disk space for the new table and the temporary work files created during the sort. The disk space needed to perform a sort can be as much as three times the size of the source table. The amount of available disk space can be determined with DISKSPACE( ) and SYS(2020). If you run out of disk space during a sort, Visual FoxPro displays an error message, and the temporary work files are deleted.

Character-type fields that contain numbers and spaces might not sort in the order you expect. Numeric fields fill from right to left, with empty spaces to the left. In contrast, character fields fill from left to right, with empty spaces to the right.

For example, if two records in a table contain a character field with 1724 in one record and 18 in the other, and the table is sorted on this field in ascending order, the record containing 1724 appears before the record containing 18. This is because Visual FoxPro reads each character in the character fields from left to right, and because 17 (in 1724) is less than 18 (in 18), it puts 1724 first. To avoid this problem, always precede lower numbers with leading zeros (0018) or make the field numeric.

See Also

COPY FILE | DISKSPACE( ) | INDEX | SYS(2020) - Default Disk Size