Ranking Sort Field Values

In this section:

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.

Syntax: How to Rank Sort Field Values 

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

where:

name

Is the new name for the RANK column title.

sortfield

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

n

Is the number of rank categories to display on the report output.

text

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

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

 

DENSE and SPARSE Ranking

How to:

Reference:

The WebFOCUS sort phrases RANK BY and BY {HIGHEST|LOWEST} n sort the report output and assign rank numbers to the sequence of data values. When assigning a rank to a data value, by default WebFOCUS does not skip rank numbers. This means that even when multiple data values are assigned the same rank, the rank number for the next group of values is the next sequential integer. This method of assigning rank numbers is called dense.

Some of the relational engines assign rank numbers using a method called sparse. With sparse ranking, if multiple data values are assigned the same rank number, the next rank number will be the previous rank number plus the number of multiples.

You can use the WebFOCUS RANK parameter to control the type of ranking done by WebFOCUS. In addition, if you are accessing a relational data source, you can set the ranking method to the type of ranking done by your relational engine so that the rank calculation can be optimized. Some relational engines have functions for both dense and sparse ranking. In this case, either setting can be optimized.

Reference: Optimizing Ranking

In order to pass rank processing to a relational engine your request must:

  • Use the SUM (or WRITE or ADD) command to aggregate values.
  • Specify the number of rank categories to be displayed. That is, you must specify a value for n: 
    [RANKED] BY [HIGHEST] n

Syntax: How to Control the Ranking Method 

SET RANK={DENSE|SPARSE}

where:

DENSE

Specifies dense ranking. With this method, each rank number is the next sequential integer, even when the same rank is assigned to multiple data values. DENSE is the default value.

SPARSE

Specifies sparse ranking. With this method, if the same rank number is assigned to multiple data values, the next rank number will be the previous rank number plus the number of multiples.

Then, in your request, use one of the following forms of the BY phrase: 

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

or

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

where:

n

Is the highest rank number to display on the report output when the RANKED BY phrase is used. When RANKED is not used, it is the number of distinct sort field values to display on the report output when SET RANK=DENSE, and the total number of lines of output for the sort field when SET RANK=SPARSE.

sortfield

Is the name of the sort field.

text

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

Reference: Usage Notes for SET RANK

  • The RNK. prefix operator is not affected by the RANK parameter.
  • The rank numbers propagated to a HOLD file depend on the RANK parameter setting.

Example: Ranking Values in a TIBCO FOCUS Data Source

The following request against the EMPDATA data source ranks salaries in descending order by division. The RANK parameter is set to DENSE (the default). 

SET RANK = DENSE           
TABLE FILE EMPDATA         
PRINT LASTNAME FIRSTNAME   
RANKED BY HIGHEST 12 SALARY
BY DIV                     
ON TABLE SET PAGE NOPAGE   
END

On the output, six employees are included in rank number 6. With dense ranking, the next rank number is the next highest integer, 7. 

RANK           SALARY  DIV   LASTNAME         FIRSTNAME
----           ------  ---   --------         ---------
   1      $115,000.00  CE    LASTRA           KAREN
   2       $83,000.00  CORP  SANCHEZ          EVELYN
   3       $80,500.00  SE    NOZAWA           JIM
   4       $79,000.00  CORP  SOPENA           BEN
   5       $70,000.00  WE    CASSANOVA        LOIS
   6       $62,500.00  CE    ADAMS            RUTH
                       CORP  CVEK             MARCUS
                             WANG             JOHN
                       NE    WHITE            VERONICA
                       SE    BELLA            MICHAEL
                             HIRSCHMAN        ROSE
   7       $58,800.00  WE    GOTLIEB          CHRIS
   8       $55,500.00  CORP  VALINO           DANIEL
                       NE    PATEL            DORINA
   9       $54,100.00  CE    ADDAMS           PETER
                       WE    FERNSTEIN        ERWIN
  10       $52,000.00  NE    LIEBER           JEFF
  11       $50,500.00  SE    LEWIS            CASSANDRA
  12       $49,500.00  CE    ROSENTHAL        KATRINA
                       SE    WANG             KATE

Running the same request with SET RANK=SPARSE produces the following output. Since rank category 6 includes six employees, the next rank number is 6 + 6. 

RANK           SALARY  DIV   LASTNAME         FIRSTNAME
----           ------  ---   --------         ---------
   1      $115,000.00  CE    LASTRA           KAREN
   2       $83,000.00  CORP  SANCHEZ          EVELYN
   3       $80,500.00  SE    NOZAWA           JIM
   4       $79,000.00  CORP  SOPENA           BEN
   5       $70,000.00  WE    CASSANOVA        LOIS
   6       $62,500.00  CE    ADAMS            RUTH
                       CORP  CVEK             MARCUS
                             WANG             JOHN
                       NE    WHITE            VERONICA
                       SE    BELLA            MICHAEL
                             HIRSCHMAN        ROSE
  12       $58,800.00  WE    GOTLIEB          CHRIS

Example: Limiting the Number of Sort Field Values

The following request against the EMPDATA data source sorts salaries in descending order by division and prints the 12 highest salaries. The RANK parameter is set to DENSE (the default). 

SET RANK = DENSE
TABLE FILE EMPDATA
PRINT LASTNAME FIRSTNAME
BY HIGHEST 12 SALARY
BY DIV
ON TABLE SET PAGE NOPAGE
END

On the output, 12 distinct salary values are displayed, even though some of the employees have the same salaries. 

     SALARY  DIV   LASTNAME         FIRSTNAME
     ------  ---   --------         ---------
$115,000.00  CE    LASTRA           KAREN    
 $83,000.00  CORP  SANCHEZ          EVELYN   
 $80,500.00  SE    NOZAWA           JIM      
 $79,000.00  CORP  SOPENA           BEN      
 $70,000.00  WE    CASSANOVA        LOIS     
 $62,500.00  CE    ADAMS            RUTH     
             CORP  CVEK             MARCUS   
                   WANG             JOHN     
             NE    WHITE            VERONICA 
             SE    BELLA            MICHAEL  
                   HIRSCHMAN        ROSE     
 $58,800.00  WE    GOTLIEB          CHRIS    
 $55,500.00  CORP  VALINO           DANIEL   
             NE    PATEL            DORINA   
 $54,100.00  CE    ADDAMS           PETER    
             WE    FERNSTEIN        ERWIN    
 $52,000.00  NE    LIEBER           JEFF     
 $50,500.00  SE    LEWIS            CASSANDRA
 $49,500.00  CE    ROSENTHAL        KATRINA
             SE    WANG             KATE

Running the same request with SET RANK=SPARSE produces the following output. Since six employees have salary $62,500, that value is counted 6 times so that only 12 lines (seven distinct salary values) display on the output. 

     SALARY  DIV   LASTNAME         FIRSTNAME
     ------  ---   --------         ---------
$115,000.00  CE    LASTRA           KAREN    
 $83,000.00  CORP  SANCHEZ          EVELYN   
 $80,500.00  SE    NOZAWA           JIM      
 $79,000.00  CORP  SOPENA           BEN      
 $70,000.00  WE    CASSANOVA        LOIS     
 $62,500.00  CE    ADAMS            RUTH     
             CORP  CVEK             MARCUS   
                   WANG             JOHN     
             NE    WHITE            VERONICA 
             SE    BELLA            MICHAEL  
                   HIRSCHMAN        ROSE     
 $58,800.00  WE    GOTLIEB          CHRIS