Sorting Rows

In this section:

How to:

Reference:

You can sort report information vertically using the BY phrase. This creates rows in your report. You can include up to 128 sort phrases (BY phrases plus ACROSS phrases) per report request (127 if using PRINT or LIST display commands).

Sort fields appear when their value changes. However, you can display every sort value using the BYDISPLAY parameter. For an example, see Controlling Display of Sort Field Values.

Syntax: How to Sort by Rows 

BY {HIGHEST|LOWEST} [n] sortfield [AS 'text']

where:

HIGHEST

Sorts in descending order.

LOWEST

Sorts in ascending order. LOWEST is the default value.

n

Specifies that only n sort field values are included in the report.

sortfield

Is the name of the sort field.

text

Is the column heading to use for the sort field column on the report output.

Reference: Usage Notes for Sorting Rows

  • When using the display command LIST with a BY phrase, the LIST counter is reset to 1 each time the major sort value changes.
  • The default sort sequence is low-to-high, with the following variations for different operating systems. In z/OS the sequence is a-z, A-Z, 0-9 for alphanumeric fields; 0-9 for numeric fields. In UNIX and Windows the sequence is 0-9, A-Z, a-z for alphanumeric fields; 0-9 for numeric. You can specify other sorting sequences, as described in Specifying the Sort Order.
  • You cannot use text fields as sort fields. Text fields are those described in the Master File with a FORMAT value of TX.
  • You can use a temporary field created by a DEFINE command, or by the DEFINE attribute in a Master File, as a sort field. In order to use a temporary field created by a COMPUTE command as a sort field, you must use the BY TOTAL phrase instead of the BY phrase.
  • If you specify several sort fields when reporting from a multi-path data source, all the sort fields must be in the same path.
  • Sort phrases cannot contain format information for fields.
  • Each sort field value appears only once in the report. For example, if there are six employees in the MIS department, a request that declares
    PRINT LAST_NAME BY DEPARTMENT

    prints MIS once, followed by six employee names. You can populate every vertical sort column cell with a value, even if the value is repeating, using the SET BYDISPLAY parameter. For details, see Controlling Display of Sort Field Values.

Example: Sorting Rows With BY

The following illustrates how to display all employee IDs by department. 

TABLE FILE EMPLOYEE
PRINT EMP_ID
BY DEPARTMENT
END

The output displays a row for each EMP_ID in each department: 

DEPARTMENT  EMP_ID   
----------  ------   
MIS         112847612
            117593129
            219984371
            326179357
            543729165
            818692173
PRODUCTION  071382660
            119265415
            119329144
            123764317
            126724188
            451123478

Using Multiple Vertical (BY) Sort Fields

You can organize information in a report by using more than one sort field. When you specify several sort fields, the sequence of the BY phrases determines the sort order. The first BY phrase sets the major sort break, the second BY phrase sets the second sort break, and so on. Each successive sort is nested within the previous one.

Example: Sorting With Multiple Vertical (BY) Sort Fields

The following request uses multiple vertical (BY) sort fields. 

TABLE FILE EMPLOYEE
PRINT CURR_SAL
BY DEPARTMENT BY LAST_NAME
WHERE CURR_SAL GT 21500
END

The output is:

DEPARTMENT  LAST_NAME               CURR_SAL
----------  ---------               --------
MIS         BLACKWOOD             $21,780.00
            CROSS                 $27,062.00
PRODUCTION  BANNING               $29,700.00
            IRVING                $26,862.00

Displaying a Row for Data Excluded by a Sort Phrase

How to:

Reference:

In a sort phrase, you can restrict the number of sort values displayed. With the PLUS OTHERS phrase, you can aggregate all other values to a separate group and display this group as an additional report row.

Syntax: How to Display Data Excluded by a Sort Phrase 

[RANKED] BY {HIGHEST|LOWEST|TOP|BOTTOM}  n srtfield [AS 'text']
            [PLUS OTHERS AS 'othertext']
            [IN-GROUPS-OF m1 [TOP n2]]
            [IN-RANGES-OF m3 [TOP n4]

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). BOTTOM is a synonym for LOWEST.

HIGHEST

Sorts in descending order, beginning with the highest value and continuing to the lowest value. TOP is a synonym for HIGHEST.

n

Specifies that only n sort field values are included in the report.

srtfield

Is the name of the sort field.

text

Is the text to be used as the column heading for the sort field values.

othertext

Is the text to be used as the row title for the "others" grouping. This AS phrase must be the AS phrase immediately following the PLUS OTHERS phrase.

m1

Is the incremental value between sort field groups.

n2

Is an optional number that defines the highest group label to be included in the report.

m3

Is an integer greater than zero indicating the range by which sort field values are grouped.

n4

Is an optional number that defines the highest range label to be included in the report. The range is extended to include all data values higher than this value.

Reference: Usage Notes for PLUS OTHERS

  • Alphanumeric group keys are not supported.
  • Only one PLUS OTHERS phrase is supported in a request.
  • In a request with multiple display commands, the BY field that has the PLUS OTHERS phrase must be the lowest level BY field in the request. If it is not, a message will display and the request will not be processed.
  • The BY ROWS OVER, TILES, ACROSS, and BY TOTAL phrases are not supported with PLUS OTHERS.
  • PLUS OTHERS is not supported in a MATCH FILE request. However, MORE in a TABLE request is supported.
  • HOLD is supported for formats PDF, PS, HTML, DOC, and WP.

Example: Displaying a Row Representing Sort Field Values Excluded by a Sort Phrase

The following request displays the top two ED_HRS values and aggregates the values not included in a row labeled Others: 

TABLE FILE EMPLOYEE
PRINT CURR_SAL LAST_NAME
  BY HIGHEST 2 ED_HRS
  PLUS OTHERS AS 'Others'
END

The output is:

ED_HRS         CURR_SAL  LAST_NAME
------         --------  ---------
 75.00       $21,780.00  BLACKWOOD
 50.00       $18,480.00  JONES
             $16,100.00  MCKNIGHT
Others      $165,924.00

Example: Displaying a Row Representing Data Not Included in Any Sort Field Grouping

The following request sorts by highest 2 ED_HRS and groups the sort field values by increments of 25 ED_HRS. Values that fall below the lowest group label are included in the Others category. All values above the top group label are included in the top group: 

TABLE FILE EMPLOYEE
PRINT CURR_SAL LAST_NAME
  BY HIGHEST 2 ED_HRS
  PLUS OTHERS AS 'Others'
IN-GROUPS-OF 25 TOP 50
END

The output is:

ED_HRS         CURR_SAL  LAST_NAME
------         --------  ---------
 50.00       $18,480.00  JONES
             $21,780.00  BLACKWOOD
             $16,100.00  MCKNIGHT
 25.00       $11,000.00  STEVENS
             $13,200.00  SMITH
             $26,862.00  IRVING
              $9,000.00  GREENSPAN
             $27,062.00  CROSS
Others       $78,800.00

If the BY HIGHEST phrase is changed to BY LOWEST, all values above the top grouping (50 ED_HRS and above) are included in the Others category: 

TABLE FILE EMPLOYEE
PRINT CURR_SAL LAST_NAME
  BY LOWEST 2 ED_HRS
  PLUS OTHERS AS 'Others'
IN-GROUPS-OF 25 TOP 50
END

The output is:

ED_HRS         CURR_SAL  LAST_NAME
------         --------  ---------
   .00        $9,500.00  SMITH
             $29,700.00  BANNING
             $21,120.00  ROMANS
             $18,480.00  MCCOY
 25.00       $11,000.00  STEVENS
             $13,200.00  SMITH
             $26,862.00  IRVING
              $9,000.00  GREENSPAN
             $27,062.00  CROSS
Others       $56,360.00
 Information Builders