>>>Hi,
>>>
>>>I can't figure out how to create a descending primary index... I can use Alter Table to create an ascending one, but it doesn't seem to support any ascending/descening clause. I can easily create it using the interface but I also want to do this programmatically so I can re create indexes easily if something goes wrong.
>>>
>>>TIA,
>>>Jonathan Chan
>>
>>Don't. Descending indices are inneficient. Create an ascending index, and then SET ORDER TO MYINDEX DESCDENDING.
>>
>>Hilmar.
>
>Are you saying that it is not possible or are you just saying that i really shouldn't? There would need to be some rewriting in quite a few places if my index is turned around, as it is currently descending.

It works (with the INDEX command; I don't know about CREATE TABLE etc.). But it is not efficient. To begin, descending indices are not used for Rushmore Optimization (this may sometimes be good, though - read my recent entry in FAQ for details).

Also, I read somewhere that descending indices are accessed more slowly.

Besides, why would you "recode" if the index doesn't exist yet? For new code, just USE MYTABLE ORDER MYORDER DESCENDING.

For a compound index, perhaps you want to sort first on one field, ascending, and then on another, descending. This can be done with some tricks, for instance: INDEX ON MyCharField + 1e6-MyNumField. Similar for dates. The index would be "ASCENDING" by definition. (In general, to order on more than one field, using an index, you have to convert everything to character.)

Regards,

Hilmar.