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

SQL Anywhere 11.0.0 » SQL Anywhere Server - SQL Reference » Using SQL » SQL functions » Alphabetical list of functions

 

PERCENT_RANK function [Ranking]

For any row X, defined by the function's arguments and ORDER BY specification, the PERCENT_RANK function determines the rank of row X - 1, divided by the number of rows in the group. The PERCENT_RANK function returns a decimal value between 0 and 1.

Syntax
PERCENT_RANK( ) OVER ( window-spec )
window-spec : see the Remarks section below
Remarks

Elements of window-spec can be specified either in the function syntax (inline), or in conjunction with a WINDOW clause in the SELECT statement. When used as a window function, you must specify an ORDER BY clause, you may specify a PARTITION BY clause, however, you can not specify a ROWS or RANGE clause. See the window-spec definition provided in WINDOW clause.

For more information about using window functions in SELECT statements, including working examples, see Window functions.

See also
Standards and compatibility
  • SQL/2003   SQL/OLAP feature T612.

Example

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) "Rank"
FROM Employees 
WHERE State IN ('NY');
DepartmentID Surname Salary Sex Rank
200 Martel 55700.000 M 0
100 Guevara 42998.000 M 0.333333333
100 Soo 39075.000 M 0.666666667
400 Ahmed 34992.000 M 1
300 Davidson 57090.000 F 0
400 Blaikie 54900.000 F 0.333333333
100 Whitney 45700.000 F 0.666666667
400 Wetherby 35745.000 F 1