CUME_DIST function [Ranking]

Function

The CUME_DIST function calculates 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 Usage section below.

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 DepartmentIDORDER BY Salary DESC) "Rank"FROM Employees WHERE State IN ('CA');

The returned result set is:

Table 4-18: CUME_DIST result set

DepartmentID

Surname

Salary

Rank

200

Savarino

72300.000

0.333333

200

Clark

45000.000

0.666667

200

Overbey

39300.000

1.000000

Usage

Sybase IQ calculates the cumulative distribution of a value of x in a set S of size N using:CUME_DIST(x) = number of values in S coming before and including x in the specified order / N

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.

You can specify elements of window-spec either in the function syntax (inline), or with a WINDOW clause in the SELECT statement. The window-spec must contain the ORDER BY clause, and cannot contain a ROWS or RANGE clause. For information on how to specify the window, see “Analytical functions”.

NoteDISTINCT is not supported.

Standards and compatibility