The Sort Clause

The Sort Clause

Top  Previous  Next


The optional sort clause determines the order in which records are inserted into the select list (SELECT, SSELECT, SEARCH) or reported (LIST, SORT, LIST.ITEM, SORT.ITEM).


Sorting is performed before conversion of data to its external format. Thus sorts of date fields, for example, will correctly sequence dates regardless of their conversion.


The justification mode of the field's format is used to determine whether a left of right aligned sort is performed. For dates, a right aligned sort is required to avoid problems with dates with internal values of differing numbers of digits.


There are two sort clause operators for single valued fields, BY and BY.DSND, which differ only in that BY sorts into ascending order and BY.DSND sorts into descending order. Similarly, there are two exploded sort operators, BY.EXP and BY.EXP.DSND, for use with multivalued fields. These explode the multivalued records to their single valued equivalents, allowing a query to process values in sequence.


Where multiple sort items are specified, the query processor examines them in the order in which they appear in the command. The second and subsequent sort items are only examined where the previous sort was not sufficient to identify the record sequence.


The SORT, SORT.ITEM and SSELECT verbs are equivalent to the LIST, LIST.ITEM and SELECT verbs with a BY @ID clause as the final sort.



The Sort Algorithm


The sorting process compares items to establish their correct position in sorted order.


When using a left aligned sort, items are compared character by character from the left hand end until a difference is encountered. The relative sort position of the two items is then determined by the collating sequence order of the characters that differ.


When using a right aligned sort, the comparison process is considerably more complex. Each item is considered to be formed from a series of elements that may be numeric or non-numeric. The numeric elements are compared as numeric values, including allowing for a leading sign character on the first element only. The non-numeric elements are compared character by character, left to right as for a left aligned sort. Where the first element of one item is numeric and the first element of the other is non-numeric, the numeric one is positioned earlier in the sorted result.


Although right aligned sorts are most commonly used with data that is entirely numeric, the above process ensures that it operates correctly with mixed data types, producing a logical and consistent sorted result. The following example shows the correct sort order for the same data using both left and right aligned sort modes.


Left:+3, -6, 103, 10A, 1943, 1A1, 1B1, 7CX, A1A, A1C, AA, BD24, BD7, BF20, XX90


Right:-6, 1A1, 1B1, +3, 7CX, 10A, 103, 1943, A1A, A1C, AA, BD7, BD24, BF20, XX90