Caché MultiValue Query Language (CMQL) Reference
BY clause
[Home] [Back] [Next]
InterSystems: The power behind what matters   
Class Reference   
Search:    

Sorts query output using a field value.
Synopsis
BY field
BY.DSND field
BY.EXP field
BY.EXP.DSND field
Description
The BY clause sorts the output returned by the query in ascending order by the values of the specified field. You can specify multiple BY clauses for nested sorting operations. Sorts are performed in the order that the BY clauses are specified. For example, BY F1 BY F3 sorts first by F1 values, then by F3 values within identical F1 values. NULL values always sort to the beginning of the sort order.
BY sorts in ascending collation sequence order. BY.DSND sorts in descending collation sequence order. BY.DSND and BY-DSND are synonyms.
BY sorts a single-value field. Use BY.EXP when sorting a multivalue dynamic array field. BY.EXP explodes each dynamic array into its own data row for the purposes of this sort operation. BY.EXP sorts in ascending collation sequence order; BY.EXP.DSND sorts in descending collation sequence order. BY.EXP and BY-EXP are synonyms. BY.EXP.DSND and BY-EXP-DSND are synonyms.
When multiple CMQL clauses are specified the clauses may be specified in any order. The order of application of CMQL clauses is as follows: the WITH clause is always applied first. Its results are supplied to the BY clause(s) (if present), which supplies its results to the SAMPLED clause (if present), and its results are supplied to the SAMPLE clause (if present).
Single-valued and Multivalued Fields
A field is defined in its SM dictionary entry as either “S” (single-valued) or “M” (multivalued). It is also possible that a field has no SM dictionary entry.
BY assumes that its field dictionary entry is a single-valued element. If field is a multivalued element, the BY clause is logically incorrect. In this case, CMQL treats field as single valued for the purpose of sorting; all the multivalues will be sorted as if they were a single string. This can sometimes be used as a 'trick' to sort by the first multivalue only. However, if you are doing this it is suggested that you review your file layout.
BY.EXP assumes that its field dictionary entry is a multivalued element. If field is a single valued field, the BY.EXP clause is logically incorrect. In this case, CMQL treats field as if it were a multivalued element.
Specifying the wrong BY or BY.EXP for the field may result in CMQL being unable to optimize the query using indexes.
When performing a sort on a child table, when a row is specified as single-valued, Caché MultiValue generates a reference to the parent table for that column. This means that a single value (the first value) is returned for every row. When displaying single values in an exploding list, single valued attributes should be designated with an “S” in the SM dictionary attribute (field 5):
Examples
The following example searches the FILENAME field of the VOC for all values that begin with the letter J, and sorts them by the values of the F1 field:
LIST VOC WITH FILENAME LIKE J... BY F1
It lists items in the following sequence: JOIN JOBS JED. (JOIN F1=K, JOBS F1=PA, JED F1=V).
The following examples sort the VOC by F1 and F3 values. The first example sorts by F1 and within that by F3 field values. The second example sorts by F3 and within that by F1 field values:
LIST VOC WITH F1 > K BY F1 BY F3
It lists items in the following sequence (partial list): @CMQLOPTS JOBS LIST.JOB LISTME LISTU COL-HDR-SUPP COL.HDR.SUPP LISTF LISTPA LISTPH LISTS ; ABORT ASSIGN AUTOLOGOUT BASIC ...
LIST VOC WITH F1 > K BY F3 BY F1
It lists items in the following sequence (partial list): @CMQLOPTS JOBS LIST.JOB LISTME LISTU COL-HDR-SUPP COL.HDR.SUPP LISTF LISTPA LISTPH LISTS RELLEVEL ; ABORT ASSIGN AUTOLOGOUT BASIC ... Notice that RELLEVEL has sorted differently than in the first example.
Multivalued Examples
The following example uses BY.EXP to sort the multivalued field AMOUNT in the SALES file:
LIST SALES AMOUNT BY.EXP AMOUNT WITH @ID < 5
It returns the following data. Note that each @ID item is exploded into as many rows as there are AMOUNT values for that item. The number of items listed is the exploded number of rows (8), not the number of records (4).
SALES..... AMOUNT.........
 
1                  $170.03
4                  $196.13
2                  $361.95
1                  $707.59
4                  $788.58
3                  $807.70
1                  $848.47
3                  $968.34
 
8 Items listed.
The following example uses BY to sort the multivalued field AMOUNT in the SALES file:
LIST SALES AMOUNT BY AMOUNT WITH @ID < 5
The BY sort treats the multivalue string as a single value. This results in a sort by the first element in the multivalue; it returns the following data. Because the multivalue data is not exploded, the number of items listed is the number of records (4):
SALES..... AMOUNT.........
 
4                  $196.13
                   $788.58
2                  $361.95
3                  $807.70
                   $968.34
1                  $848.47
                   $170.03
                   $707.59
 
4 Items listed.
The following example first uses BY to sort the single-value @ID field, then uses BY.EXP to sort the multivalue AMOUNT field:
LIST SALES AMOUNT BY @ID BY.EXP AMOUNT WITH @ID < 5
It returns the following data:
SALES..... AMOUNT.........
 
1                  $170.03
1                  $707.59
1                  $848.47
2                  $361.95
3                  $807.70
3                  $968.34
4                  $196.13
4                  $788.58
 
8 Items listed.
Emulation
When exploding, UniVerse and UniData explode subvalues, but D3, Reality, and jBASE only explode values. Reality provides a BY.EXP.SUB keyword to explode by subvalues which gives the same result as a simple BY.EXP on UniVerse.