Ranking Sort Field Values

How to:

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 digits. For more information, see Using Headings, Footings, Titles, and Labels.

You can rank aggregated values using the syntax RANKED BY TOTAL. For details, see Sorting and Aggregating Report Columns.


Top of page

x
Syntax: How to Rank Sort Field Values
RANKED [AS 'name'] BY sortfield

where:

sortfield

Is the name of the sort field. The field can be numeric or alphanumeric.

name

Is the new name for the RANK column title.



Example: Ranking Sort Field Values

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

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

The output is:



Example: Ranking and Restricting Sort Field Values

Ranking sort field values is frequently combined with restricting sort field values by rank, as in the following example.

TABLE FILE EMPLOYEE
PRINT LAST_NAME
RANKED BY HIGHEST 5 CURR_SAL
END

The output is:

RANK         CURR_SAL  LAST_NAME
----         --------  ---------
   1       $29,700.00  BANNING  
   2       $27,062.00  CROSS    
   3       $26,862.00  IRVING   
   4       $21,780.00  BLACKWOOD
   5       $21,120.00  ROMANS

WebFOCUS