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.

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

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.

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

Example

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:

DepartmentID Surname Salary Rank
200 Savarino 72300.000 0.333333333333333
200 Clark 45000.000 0.666666666666667
200 Overbey 39300.000 1