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

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. .

PERCENT_RANK( ) OVER ( window-spec )

window-spec : see the Remarks section below

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.

• CUME_DIST function [Ranking]
• DENSE_RANK function [Ranking]
• RANK function [Ranking]

• SQL/2003    SQL/OLAP feature T612

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');