Using the sort_by Column to Specify a Sort Order

The sort order specifies the collating sequence used to order the data in the result set. The default sort order is set by the sort_order configuration parameter (for more information, see “Setting the Default Sort Order”). Case insensitive sort order is supported in the Enhanced version.

Use the sort_by pseudo column to return a result set with a sort order other than the default. With the Enhanced Full-Text Search engine, you can specify up to 16 sort specifications in the sort_by pseudo column.

Table 5-2 lists the values for the sort_by pseudo column.

Table 5-2: Values for the sort_by pseudo column

Value

Description

fts_score desc

Returns a result set sorted by score in descending order.

fts_score asc

Returns a result set sorted by score in ascending order.

fts_timestamp desc

Returns a result set sorted by a timestamp in descending order.

fts_timestamp asc

Returns a result set sorted by a timestamp in ascending order.

column_name desc

Returns a result set sorted according to the descending order of a column. column_name is the name of the source table’s column.

column_name asc

Returns a result set sorted according to the ascending order of a column. column_name is the name of the source table’s column.

fts_cluster asc

Returns a clustered result set. Only available with the Enhanced Full-Text Search engine. (See “Using Pseudo Columns to Request Clustered Result Sets” for more information.)

NoteBefore you can sort by specific columns, you must modify the style.vgw and style.ufl files (see “Setting Up a Column to Use As a Sort Specification”).

For example, the following query sorts the documents by timestamp in ascending order:

select t1.score, t2.copy
from i_blurbs t1, blurbs t2
where t1.id=t2.id and t1.score > 90
and t1.index_any = "<accrue>(raconteur, Paris)"
and t1.sort_by = “fts_timestamp asc”