Computes the relative position of one value among a group of rows.
CUME_DIST( ) OVER ( window-spec )
window-spec : see the Remarks section below
A DOUBLE value between 0 and 1
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.
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:
DepartmentID | Surname | Salary | Rank |
---|---|---|---|
200 | Savarino | 72300.000 | 0.333333333333333 |
200 | Clark | 45000.000 | 0.666666666666667 |
200 | Overbey | 39300.000 | 1 |
Discuss this page in DocCommentXchange. Send feedback about this page using email. |
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 |