Ranking Sort Field Values

When you sort report rows using the BY phrase, you can indicate the numeric rank of each row. Ranking sort field values is frequently combined with restricting sort field values by rank.

Note that it is possible for several report rows to have the same rank if they have identical sort field values.

The default column title for RANKED BY is RANK. You can change the title using an AS phrase. The RANK field has format I7. Therefore, the RANK column in a report can be up to seven (7) digits.

You can rank aggregated values using the syntax RANKED BY TOTAL.

The format of the RANKED command is:

RANKED [AS 'name'] BY {HIGHEST|LOWEST|TOP|BOTTOM} [n] sort_field
[AS 'text']

TOP is a synonym for HIGHEST and BOTTOM is a synonym for LOWEST.

To display a list of employee names in salary order, indicating the rank of each employee by salary, issue the following request. Note that employees Jones and McCoy have the same rank since their current salary is the same.

TABLE FILE EMPLOYEE
PRINT LAST_NAME 
BY DEPARTMENT 
RANKED AS 'Sequence' BY CURR_SAL
END

Run the request. The output is:

To rank sort field values while restricting sort field values by rank, issue the following request:

TABLE FILE EMPLOYEE
PRINT LAST_NAME
BY DEPARTMENT
RANKED BY HIGHEST 3 CURR_SAL
END

Run the request. The output is:

Notice that the rank resets based on the outer BY field, DEPARTMENT.