Sorts records in the currently selected table and outputs the sorted records to a new table.
SORT TO TableNameON FieldName1 [/A | /D] [/C] [, FieldName2 [/A | /D] [/C] ...] [ASCENDING | DESCENDING] [Scope] [FOR lExpression1] [WHILE lExpression2] [FIELDS FieldNameList | FIELDS LIKE Skeleton | FIELDS EXCEPT Skeleton] [NOOPTIMIZE]
- 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
customertable is opened and sorted, creating a new table named
temp. The records in
tempare ordered by the
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
clientsis created. The
orderstable is sorted on the
order_datefield in ascending order and the
freightfield in descending order.
USE orders SORT TO clients ON order_date/A,freight/D
- Specifies an ascending order for all fields not followed by /D.
- 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.
- 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 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*
- Disables Rushmore optimization of SORT.
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.