Specifying the Sort Order

Sort field values are automatically displayed in ascending order, beginning with the lowest value and continuing to the highest. The default sorting sequence varies for operating systems. On z/OS and VM it is a-z, A-Z, 0-9 for alphanumeric fields; 0-9 for numeric fields. On UNIX and Windows it is 0-9, A-Z, a-z for alphanumeric fields; 0-9 for numeric fields.

You have the option of overriding this default and displaying values in descending order, ranging from the highest value to the lowest value, by including HIGHEST in the sort phrase.

Syntax: How to Specify the Sort Order

{BY|ACROSS} {LOWEST|HIGHEST} sortfield

where:

LOWEST: Sorts in ascending order, beginning with the lowest value and continuing to the highest value (a-z, A-Z, 0-9 for alphanumeric fields; 0-9 for numeric fields). This option is the default.
HIGHEST: Sorts in descending order, beginning with the highest value and continuing to the lowest value. You can also use TOP as a synonym for HIGHEST.
sortfield: Is the name of the sort field.

Top of page

Example: Sorting in Ascending Order

The following report request does not specify a particular sorting order, and so, by default, it lists salaries ranging from the lowest to the highest.

TABLE FILE EMPLOYEE
PRINT LAST_NAME
BY CURR_SAL
END

You can specify this same ascending order explicitly by including LOWEST in the sort phrase.

TABLE FILE EMPLOYEE
PRINT LAST_NAME
BY LOWEST CURR_SAL
END

The output is:

  CURR_SAL  LAST_NAME
  --------  ---------
 $9,000.00  GREENSPAN
 $9,500.00  SMITH    
$11,000.00  STEVENS  
$13,200.00  SMITH    
$16,100.00  MCKNIGHT 
$18,480.00  JONES    
            MCCOY    
$21,120.00  ROMANS   
$21,780.00  BLACKWOOD
$26,862.00  IRVING   
$27,062.00  CROSS    
$29,700.00  BANNING

Top of page

Example: Sorting in Descending Order

The following request lists salaries ranging from the highest to lowest.

TABLE FILE EMPLOYEE
PRINT LAST_NAME
BY HIGHEST CURR_SAL
END

The output is:

  CURR_SAL  LAST_NAME
  --------  ---------
$29,700.00  BANNING  
$27,062.00  CROSS    
$26,862.00  IRVING   
$21,780.00  BLACKWOOD
$21,120.00  ROMANS   
$18,480.00  JONES    
            MCCOY    
$16,100.00  MCKNIGHT 
$13,200.00  SMITH    
$11,000.00  STEVENS  
 $9,500.00  SMITH    
 $9,000.00  GREENSPAN

Specifying Your Own Sort Order

How to:

Reference:

Sort field values are automatically displayed in ascending order, beginning with the lowest value and continuing to the highest.

You can override the default order and display values in your own user-defined sorting sequence. To do this, you need to decide the following:

Which sort field values you want to allow. You can specify every sort field value, or a subset of values. When you issue your report request, only records containing those values are included in the report.
The order in which you want the values to appear. You can specify any order; for example, you could specify that an A1 sort field containing a single-letter code be sorted in the order A, Z, B, C, Y...

There are two ways to specify your own sorting order, depending on whether you are sorting rows with BY, or sorting columns with ACROSS:

The BY ROWS OVER phrase, for defining your own row sort sequence.
The ACROSS COLUMNS AND phrase, for defining your own column sort sequence.

Top of page

Syntax: How to Define Your Own Sort Order

BY sortfield ROWS value1 OVER value2 [... OVER valuen]

where:

sortfield: Is the name of the sort field.
value1: Is the sort field value that is first in the sorting sequence.
value2: Is the sort field value that is second in the sorting sequence.
valuen: Is the sort field value that is last in the sorting sequence.

An alternative syntax is

FOR sortfield value1 OVER value2 [... OVER valuen]

which uses the row-based reporting phrase FOR, described in Creating Financial Reports With Financial Modeling Language (FML).

Top of page

Reference: Usage Notes for Defining Your Sort Order

Any sort field value that you do not specify in the BY ROWS OVER phrase is not included in the sorting sequence, and does not appear in the report.
Sort field values that contain embedded blank spaces should be enclosed in single quotation marks.
Any sort field value that you do specify in the BY ROWS OVER phrase is included in the report, whether or not there is data.
The name of the sort field is not included in the report.
Each report request can contain only one BY ROWS OVER phrase. BY ROWS OVER is not supported with the FOR phrase. For information about the FOR phrase, see Creating Financial Reports With Financial Modeling Language (FML).

Top of page

Example: Defining Your Row Sort Order

The following illustrates how to sort employees by the banks at which their paychecks are automatically deposited, and how to define your own label in the sorting sequence for the bank field.

TABLE FILE EMPLOYEE
PRINT LAST_NAME
BY BANK_NAME ROWS 'BEST BANK' OVER STATE
   OVER ASSOCIATED OVER 'BANK ASSOCIATION'
END

The output is:

                  LAST_NAME
                  ---------
BEST BANK         BANNING  
STATE             JONES    
ASSOCIATED        IRVING   
ASSOCIATED        BLACKWOOD
ASSOCIATED        MCKNIGHT 
BANK ASSOCIATION  CROSS

Top of page

Syntax: How to Define Column Sort Sequence

ACROSS sortfield COLUMNS value1 AND value2 [... AND valuen]

where:

sortfield: Is the name of the sort field.
value1: Is the sort field value that is first in the sorting sequence.
value2: Is the sort field value that is second in the sorting sequence.
valuen: Is the sort field value that is last in the sorting sequence.

Top of page

Reference: Usage Notes for Defining Column Sort Sequence

Any sort field value that you do not specify in the ACROSS COLUMNS AND phrase is not included in the label within the sorting sequence, and does not appear in the report.
Sort field values that contain embedded blank spaces should be enclosed in single quotation marks.
Any sort field value that you do specify in the ACROSS COLUMNS AND phrase is included in the report, whether or not there is data.
When using a COMPUTE with an ACROSS COLUMNS phrase, the COLUMNS should be specified last:
```
ACROSS acrossfield [AND] COMPUTE compute_expression; COLUMNS values
```
Each report request may contain only one BY ROWS OVER phrase.

Top of page

Example: Defining Column Sort Sequence

The following illustrates how to sum employee salaries by the bank at which they are automatically deposited, and to define your own label within the sorting sequence for the bank field.

TABLE FILE EMPLOYEE
SUM CURR_SAL
ACROSS BANK_NAME COLUMNS 'BEST BANK' AND STATE
   AND ASSOCIATED AND 'BANK ASSOCIATION'
END

The output is:

BANK_NAME                                                                    
BEST BANK          STATE              ASSOCIATED         BANK ASSOCIATION
-------------------------------------------------------------------------
     $29,700.00         $18,480.00         $64,742.00         $27,062.00