How to Create an Index in Descending Order

• Microsoft FoxBASE+ for MS-DOS, version 2.10
• Microsoft FoxPro for MS-DOS, version 1.02

SUMMARY

By default, indexes are created in ascending order (for example, alphabetically from A to Z) or by date from January to December. However, in some cases, there is a need to create an index in reverse (descending order such as alphabetically from Z to A) or by date from December to January. This article shows you how to create an index in descending order.

MORE INFORMATION

Numeric Fields

To create a reverse index on a numeric field, create a value that is the difference between an arbitrary large value and the value being indexed. For example, if SALES is a numeric field in a database and MyIndex is an indexname, the following creates a reverse index on sales:

   INDEX ON 10000000 - SALES TO MyIndex

Date Fields

The same principle applies to create a reverse index on a date field. If lastsale is a field in the database and MyIndex is an indexname, the following code creates a reverse index on the date field:

   INDEX ON CTOD("01/01/2100") - lastsale TO MyIndex

Character Fields

The procedure to create a reverse index on a character field requires three steps:

1. Create the following program called MCOMP.PRG to create a 255 character string, as follows:

         PRIVATE char, mdescend
         char = 255
         mdescend = ""
         DO WHILE char >= 1
            mdescend = mdescend + CHR(char)
            char = char - 1
         ENDDO
         RETURN mdescend
   
   

2. Assign the string generated by this program to a variable, as follows:

         mdesc_ord = mcomp()
   

   Note: This variable must be created each time the descending index is used. Otherwise, the program returns a "Variable Not Found" error.

3. Create the index using the SYS(15) function, the variable, and the database field.

   For example, if the mdesc_ord variable is set as indicated in Step 2 above, lname is a field in the database and MyIdex is the indexname, the following code creates a reverse index on the lname field:

         INDEX ON SYS(15,mdesc_ord,lname) TO MyIndex
   

   Note: The SYS(15,<expC1>,<expC2) function takes each character of the string <expC2> and uses the character's numeric value as a subscript into the table <expC1>, replacing the character in <expC2> with the character found at that position in the table.

Index on Two Fields [Field1 Ascending, Field2 Descending]

To create an index on two fields where the first field is indexed in ascending order and the second field is indexed in descending order, refer to the character field example above. The same concept could apply to more than two fields. Follow Steps 1 and 2 above, and then Step 3 below:

1. Create the index using the first field, the SYS(15), the variable, and the second field.

   For example, if the mdesc_ord variable is set as indicated in Step 2 above, fname is the first field to be indexed in ascending order, lname is the second field to be indexed in descending (reverse) order, and MyIndex is the indexname, the following code creates the index:

         INDEX ON fname+SYS(15,mdesc_ord,lname) TO MyIndex