Specifying Index Sort Order

Go Up to Using CREATE INDEX

Specify a direction (low to high or high to low) by using the ­ASCENDING or DESCENDING keyword. By default, InterBase creates indexes in ascending order. To make a descending index on a column or group of columns, use the DESCENDING keyword to define the index. The following statement creates a descending index (DESC_X) on the CHANGE_DATE column of the ­SALARY_HISTORY table:

CREATE DESCENDING INDEX DESC_X ON SALARY_HISTORY (CHANGE_DATE);

Note: To retrieve indexed data from this table in descending order, use ORDER BY CHANGE_DATE DESCENDING in the SELECT statement.

If you intend to use both ascending and descending sort orders on a particular column, define both an ascending and a descending index for the same column. The following example illustrates this:

CREATE ASCENDING INDEX ASCEND_X ON SALARY_HISTORY (CHANGE_DATE);
CREATE DESCENDING INDEX DESC_X ON SALARY_HISTORY (CHANGE_DATE);