SORT Command

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.

 CopyCode imageCopy Code
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.

 CopyCode imageCopy Code
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:

 CopyCode imageCopy Code
SORT TO mytable ON myfield FIELDS LIKE A*,P*

The LIKE clause can be combined with the EXCEPT clause:

 CopyCode imageCopy Code
SORT TO mytable ON myfield FIELDS LIKE A*,P* EXCEPT PARTNO*
NOOPTIMIZE

Disables Rushmore optimization of SORT.

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

Expand imageRemarks

Expand imageSee Also


© , 1996-2020 • Updated: 11/10/20
Comment or report problem with topic