Sort Criteria
Sort criteria specify the order you wish the data to be listed in the report. A sort criterion consists of a sort connective followed by an attribute name. The sort connective specifies sorting in ascending or descending sequence. If you include more than one sort criterion, the processor combines them into one sort key.
The sorting sequence differs depending on whether the attribute is left- or right-aligned. Numerical data, such as dates stored in internal format, should always be right-aligned if required to sort in correct numerical sequence.
Sort connectives are of three types: those appropriate for single valued attributes, those for multivalued attributes, and those for subvalued attributes.
Note
-
If you just want to sort by item-id, you can use a command that sorts such as SORT, SSELECT or SORT-LABEL and omit any sort criteria.
-
If you include one or more sort criteria, detail lines in which all sort values are the same are finally sorted by item-id.
-
When sorting by item-id, if the file specified in the English sentence has case-insensitive item-ids, the case of item-ids is ignored. For example, if a file contains items with ids "ddd", "AAA", "CCC" and "bbb",
SORT FILE
will sort the items into the order "AAA", "bbb", "CCC", "ddd", whereas a case-sensitive sort would produce "AAA", "CCC", "bbb", "ddd". See Case Sensitivity for more details.
Single Value Sort Connectives
BY Sorts the specified attributes into an ascending order (lowest value first).
BY-DSND Sorts the specified attributes into a descending order (highest value first).
Multivalue Sort Connectives
BY-EXP Sorts the values in the specified attribute into an ascending order (lowest value first).
BY-EXP-DSND Sorts the values in the specified attribute into a descending order (highest value first).
Subvalue Sort Connectives
BY-EXP-SUB Sorts the subvalues in the specified attribute in ascending order.
BY-EXP-SUB-DSND
Sorts the subvalues in the specified attribute in descending order.
Default Sort Order
If you do not specify a sort criteria clause for a sorting
command, the report is output in ascending order by
Sort Order of Left-aligned Data
When the processor sorts a left-aligned attribute, it compares the data one character at a time, left to right. For this reason, the number 4 follows 32 in ascending sequence.
If a denationalisation collation table has been specified, the processor uses it to translate the characters before making the comparison.
Sort Order of Right-aligned Data
When the processor sorts a right-aligned attribute, it compares the numeric data separately from the alphabetic data. It compares the alphabetic data one character at a time, left to right, translating the characters if a denationalisation table has been specified.
The processor compares numeric data by magnitude, including sign, rather than a character at a time. In this way the number 4 precedes 32 in ascending sequence.
If the data contains both numeric and alphabetic characters, the processor compares the different parts of the data separately. For this reason, in ascending sequence AA4AA precedes AA004AB, which precedes AA32AA.
Multiple Key Sort
You may include any number of sort criteria in the sentence. The processor uses the first as the highest order sort value, and continues, in order, toward the end of the sentence. The processor always uses the item-id as the lowest order sort value.
Sorting Single Valued Attributes
The sort connectives for single values treat each attribute as though it had only one value. There are two sort connectives for single values:
-
BY for ascending sort
-
BY-DSND for descending sort.
Syntax
BY attribute
or
BY-DSND attribute
Syntax Elements
attribute The item-id of a data definition item.
Multivalued Attribute
If a single value sort connective is used on a multivalued attribute, the data is sorted by the first subvalue in the first value.
Examples
The first example illustrates the use of a single sort criteria clause. The GUESTS file is sorted by arrival date and the report lists the item-ids (room number) and default headings. Dates are shown in this example in US format.
:SORT GUESTS BY ARRIVE-DATE
PAGE 1
GUESTS Guest Name Address City Arrival
Date
117 Mr. and Mrs. H. Irving 20 Thorpe Rd Lexington 08/30/94
211 David M. Lewis 40 Lakeview Dr. Chicago 08/30/94
. . . . .
140 Susan P. Lynch 55 Hale Rd Waltham 09/01/94
143 William Hennessey 11 Elliston Rd Lexington 09/01/94
478 Harold F. Kolman 123 Potsdamer Str Berlin 10/12/94
26 ITEMS LISTED
The following sentence has two sort criteria. The room rate is sorted into a descending order, but the last names of the guests are sorted into an ascending (alphabetical) order.
:SORT ROOMS WITH BED-CODE # "W]" BY-DSND RATE BY GUEST-NAME
BED-CODE RATE GUEST-NAME (HP
ROOMS Bed Rate... Current Guests ..
Code
321 K 82.00
444 K 82.00 Curtis
289 K 82.00 Fennelly
. . . .
142 K 72.00 Madison
117 K 72.00 Rizzo
143 Q 68.00 Hennessey
. . . .
Sorting Multivalued Attributes
The sort connectives for multivalued attributes sort values within an attribute. The processor treats each value as though it were the only one so that an entire line of data is listed for each value, effectively "exploding" an item into multiple items. If there are subvalues in a value, the processor looks at only the first one. There are two sort connectives for multivalues:
-
BY-EXP for ascending sort,
-
BY-EXP-DSND for descending sort.
You can include a print limiter with one multivalue sort connective in an English sentence. This limits the values that the processor sorts and lists.
When using a SELECT-type command with the BY-EXP or BY-EXP-DSND connective on a multivalued attribute, the list has the format:
item-id]multiValue#
where multiValue# is the position of the multivalue within the attribute specified by BY-EXP or BY-EXP-DSND. This value can be used by the READNEXT statement in a DataBasic program. For information, see DataBasic Reference.
Syntax
BY-EXP attribute {printLimiter}
or
BY-EXP-DSND attribute {printLimiter}
Syntax Elements
attribute The item-id of a data definition item.
{NOT} {relationaloperator} "valueString"
{{logicalConnective} {NOT} {relationalOperator}
"valueString"}...
Rule
Use only one multivalue sort connective with a print limiter in an English sentence. If you use more than one sort connective with a print limiter, the results are unpredictable.
Multivalues with Subvalues
If the values in an attribute have subvalues, the processor sorts the values by the first subvalue in each value and only the first subvalue.
Examples of Sorting Multivalued Attributes
The following example illustrates the use of a multivalue connective on multivalued attributes:
:SORT GUESTS BY-EXP BILL-CODE BILL-CODE BILL-AMOUNT PAGE 1 GUESTS Bill Amount.. Code * 117 2 $62.00 119 2 $56.00 122 2 $56.00 140 2 $55.00 . . . 117 18 $8.76 140 18 $18.76 143 18 $2.95 142 20 $45.50 101 ITEMS LISTED
Notice that the Bill Code field is listed in ascending order, and that there is a line for each value in the attribute: in this example, item-id 117 is listed twice, once for a Bill Code of 2 and once for a Bill Code of 18.
In the next example a single-value connective is used on the same multivalued attributes as the previous example. The resulting report lists items in order by the first billing code. Also note that each item is listed only once: the processor prints a line for each value in the BILL-CODE attribute.
:SORT GUESTS BY BILL-CODE BILL-CODE BILL AMOUNT
PAGE 1
GUESTS Bill Amount..
Code
*
117 2 $62.00
6 $17.95
18 $8.76
119 2 $56.00
15 $23.98
13 $17.95
140 2 $55.00
6 $34.95
. . .
The following example shows the effect of using a print limiter. The first command, without a print limiter, lists 93 items. The second command, using a limiter, lists only five items.
:SORT GUESTS BY ROOM BY-EXP BILL-CODE ROOM BILL-CODE
BILL-AMOUNT (I)
PAGE 1
Room Bill Amount..
Code
*
117 2 $62.00
117 6 $17.95
117 18 $8.76
119 2 $56.00
. . .
478 15 $2.15
535 2 $98.00
535 2 $98.00
535 5 $6.65
535 13 $45.00
535 15 $33.77
93 ITEMS LISTED.
:SORT GUESTS BY ROOM BY-EXP BILL-CODE > "15" ROOM BILL-CODE
BILL-AMOUNT (I)
PAGE 1
Room Bill Amount
Code
*
117 18 $8.76
140 18 $18.76
142 20 $45.50
143 18 $2.95
144 16 $12.05
5 ITEMS LISTED.
Sorting Subvalued Attributes
To sort by subvalue, use BY-EXP-SUB or BY-EXP-SUB-DSND. These function in a similar way to BY-EXP and BY-EXP-DSND, and have similar syntax, but generate a detail line for each subvalue.