Sorting

QM provides sorting options in the query processor and in the QMBasic programming language.

Left aligned

A simple left justified comparison, examining corresponding characters from the start of each string until either a difference is found or the end of both strings has been reached.

Right aligned

A simple right justified comparison in which the shorter item is effectively padded with leading spaces and the resultant strings are compared character by character from the start until either a difference is found or the end of both strings has been reached. If both strings can be treated as integer values, possibly with a leading sign character, a numeric comparison is performed.

Compound

A compound sort in which the two strings are considered to be formed from a series of alternating integer numeric and non-numeric elements. Numeric elements are sorted into numerical value, non-numeric elements are sorted into collating sequence order. If the first element is numeric, it may have an optional leading sign character. Sign characters appearing later in the strings are treated as being non-numeric characters. Compound sorts are implemented inconsistently across different multivalue database products, particularly with regard to the relative placement of items such as "0" and "+0". In most situations, these differences are irrelevant.

The query processor provides four sort clause options; BY, BY.DSND, BY.EXP and BY.EXP.DSND. The first two sort simple data into ascending or descending order respectively. The second two work on exploded data from multivalued data items. The sort operation will be performed in left aligned style unless the field being sorted is defined as right aligned in which case a compound sort is used.

Alternate key indices sort the indexed values using a compound sort. Within any index entry, record ids are sorted using either left or right aligned sort mode depending on the justification specified in the dictionary.

QMBasic provides two methods of sorting. The !SORT() subroutine sorts the items in a dynamic array based on a sort style and other options specified using a sort rule parameter. This also allows removal of duplicate items, case insensitivity, choice of ascending or descending sequence and omission of null items.

The second sort method uses the concept of a sort session in which a sort is initialised using the SORT() function which allows up to 32 keys for nested sorting criteria plus an optional data element. Items are added to the sort using the SORTADD statement. Once all the data has been added, items may be extracted individually with the SORTNEXT() function or as a field mark delimited dynamic array using the SORTDATA() function.

It is valid for a single QM process to have multiple sorts active simultaneously though this can lead to high memory and disk space usage. It is recommended that sort sessions that are no longer required are terminated using SORTCLEAR. A sort session is automatically terminated if the sort variable returned by SORT() is overwritten or discarded.

The default behaviour of QM is that the collating sequence is simply the numeric value of each character. This does not allow, for example, the French é (character 233) to appear next to its unaccented equivalent. Application developers can use the EDIT.MAP command to modify or replace the character map to specify alternative collating sequence rules. It is valid for different users of the same application to have different maps and hence see sorted data in a different order. Character maps for common languages can be downloaded from the openqm.com web site.