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

SQL Anywhere 11.0.1 (Français) » SQL Anywhere Server - SQL Usage » Querying and Modifying Data » OLAP support » Window functions in SQL Anywhere » Window ranking functions

 

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)/(–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:

DepartmentID Surname Salary Sex PctRank
1 200 Martel 55700.000 M 0.0
2 100 Guevara 42998.000 M 0.333333333
3 100 Soo 39075.000 M 0.666666667
4 400 Ahmed 34992.000 M 1.0
5 300 Davidson 57090.000 F 0.0
6 400 Blaikie 54900.000 F 0.333333333
7 100 Whitney 45700.000 F 0.666666667
8 400 Wetherby 35745.000 F 1.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:

Surname Salary Rank
1 Shishov 72995.00 0
2 Jordan 51432.00 0.25
3 Hildebrand 45829.00 0.5
4 Bigelow 31200.00 0.75
5 Bertrand 29800.00 1
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:

Surname Salary Percent
1 Scott 96300.00 0
2 Sheffield 87900.00 0.025
3 Lull 87900.00 0.025

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