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

CUME_DIST function [Ranking]

Computes the relative position of one value among a group of rows. It returns a decimal value between 0 and 1.

CUME_DIST( ) OVER ( window-spec )

window-spec : see the Remarks section below

Composite sort keys are not currently allowed in the CUME_DIST function. You can use composite sort keys with any of the other rank functions.

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.

• DENSE_RANK function [Ranking]
• PERCENT_RANK function [Ranking]
• RANK function [Ranking]

• SQL/2003    SQL/OLAP feature T612

The following example returns a result set that provides a cumulative distribution of the salaries of employees who live in California.

SELECT DepartmentID, Surname, Salary,
CUME_DIST() OVER (PARTITION BY DepartmentID
ORDER BY Salary DESC) "Rank"
FROM Employees 
WHERE State IN ('CA');

Here is the result set: