Sort

This is an oldie-but-baddie command. Back in the days before indexes, it was an awfully important command, but it’s been superseded so many times, we’ve lost count.

SORT makes a copy of a table, physically ordering the records in the new table according to specified criteria. SORT is a resource hog—it can require as much as three times the disk space occupied by the table to do the copying. Because you can do the same thing with COPY TO and an index, or SELECT-SQL, there’s not much reason to use SORT.

In Visual FoxPro, SORT does have one pair of cool new clauses (FIELDS LIKE, FIELDS EXCEPT), but COPY TO has them, too. (Actually, FIELDS LIKE and FIELDS EXCEPT were added in FoxPro 2.6, but Microsoft didn’t bother to tell very many people about them, then.)

Usage

SORT TO cTable
        ON uFieldName1 [ / A | D ] [ /C ]
        [, uFieldName2 [ /A | D ] [ /C ]  [ , ... ] ]
        [ ASCENDING | DESCENDING ]
        [ Scope ]
        [ FOR lForExpression ]
        [ WHILE lWhileExpression ]
        [ FIELDS cFieldList | LIKE cInclude | EXCEPT cExclude ]
        [ NOOPTIMIZE ]

We believe SORT has no place in any application, so we’re not going to review all the clauses in detail. In brief, here’s what SORT does.

Those records of the table open in the current work area that meet the Scope, FOR and WHILE clauses are copied to cTable. Only those fields either listed in cFieldList or included by the FIELDS LIKE and FIELDS EXCEPT clauses are copied.

The new table is ordered based on the ON clause. Records are sorted first by uFieldName1. Where that field is identical, records are sorted on uFieldName2. Where both are identical, the next listed field is used, and so forth. For each sort field, you specify independently where it should be applied in ascending (/A) or descending (/D) order, and whether the sort is case-insensitive (/C). The default is ascending order and case-sensitive. Combine A or D with C behind a single slash (/AC or /DC).

The ASCENDING and DESCENDING clauses determine the sort order for fields that don’t specify it explicitly.

As always, NOOPTIMIZE is mostly useless.

Example

USE Employee   && small enough to make this not too painful
SORT ON Last_Name /C, First_Name /C TO EmpName

* Here's a better way to do that
SELECT * FROM Employee ;
   ORDER BY Last_Name, First_Name ;
   INTO TABLE EmpName

See Also

Copy To, Index, Select-SQL, Set Optimize