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).
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.
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 
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].
Discuss this page in DocCommentXchange.

Copyright © 2010, iAnywhere Solutions, Inc.  SQL Anywhere 12.0.0 