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.

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:

Sequence         CURR_SAL  LAST_NAME
--------         --------  ---------
       1        $9,000.00  GREENSPAN
       2        $9,500.00  SMITH    
       3       $11,000.00  STEVENS  
       4       $13,200.00  SMITH    
       5       $16,100.00  MCKNIGHT 
       6       $18,480.00  JONES    
                           MCCOY    
       7       $21,120.00  ROMANS   
       8       $21,780.00  BLACKWOOD
       9       $26,862.00  IRVING   
      10       $27,062.00  CROSS    
      11       $29,700.00  BANNING


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

Top of page

x
DENSE and SPARSE Ranking

How to:

Reference:

The FOCUS sort phrases, RANK BY and BY {HIGHEST|LOWEST} n, produce sorted report output and assign rank numbers to the sequence of data values. When assigning a rank to a data value, FOCUS does not skip rank numbers, by default. 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 FOCUS RANK parameter to control the type of ranking done by FOCUS. 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.



x
Reference: Optimizing Ranking

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



x
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} nsortfield [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.



x
Reference: Usage Notes for SET RANK


Example: Ranking Values in a 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       

Information Builders