Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Usage » OLAP Support » Window functions » Window ranking functions

CUME_DIST function Next Page

PERCENT_RANK function


Similar to the PERCENT function, the PERCENT_RANK function returns the rank for the value in the column specified in the window's ORDER BY clause, but expressed as a fraction between 0 an 1, calculated as (RANK - 1)/(n-1).

As the window moves down the input rows, the rank is calculated for the expression specified in the window's ORDER BY clause. If the ORDER BY clause includes multiple expressions, the second and subsequent expressions are used to break ties if the first expression has the same value in adjacent rows. NULL values are sorted before any other value (in ascending sequence).

Example 1

The following example returns a result set that shows the ranking of New York employees' salaries by gender. The results are ranked in descending order using a decimal percentage, and are partitioned by gender.

SELECT DepartmentID, Surname, Salary, Sex,
    PERCENT_RANK() OVER ( PARTITION BY Sex
      ORDER BY Salary DESC ) AS PctRank
  FROM Employees 
  WHERE State IN ( 'NY' );

This query returns the following results:

DepartmentIDSurnameSalarySexPctRank
1200Martel55700.000M0.0
2100Guevara42998.000M0.333333333
3100Soo39075.000M0.666666667
4400Ahmed34992.000M1.0
5300Davidson57090.000F0.0
6400Blaikie54900.000F0.333333333
7100Whitney45700.000F0.666666667
8400Wetherby35745.000F1.0

Since the input is partitioned by gender (Sex), PERCENT_RANK is evaluated separately for males and females.

Example 2

The following example returns a list of female employees in Utah and Arizona and ranks them in descending order according to salary. Here, the PERCENT_RANK function is used to provide a cumulative total in descending order.

SELECT Surname, Salary, 
     PERCENT_RANK () OVER ( ORDER BY Salary DESC ) "Rank"
     FROM Employees 
WHERE State IN ( 'UT', 'AZ' ) AND Sex IN ( 'F' );

This query returns the following results:

SurnameSalaryRank
1Shishov72995.000
2Jordan51432.000.25
3Hildebrand45829.000.5
4Bigelow31200.000.75
5Bertrand29800.001
Using PERCENT_RANK to find top and bottom percentiles

You can use PERCENT_RANK to find the top or bottom percentiles in the data set. In the following example, the query returns male employees whose salary is in the top five percent of the data set.

SELECT * 
FROM ( SELECT Surname, Salary, 
       PERCENT_RANK () OVER ( ORDER BY Salary DESC ) "Rank"
       FROM Employees 
       WHERE Sex IN ( 'M' )  ) 
       AS DerivedTable ( Surname, Salary, Percent )
WHERE Percent < 0.05;

This query returns the following results:

SurnameSalaryPercent
1Scott96300.000
2Sheffield87900.000.025
3Lull87900.000.025

For more information on the syntax for the PERCENT_RANK function, see PERCENT_RANK function [Ranking].