## 10.6. Ranking Functions

The ranking functions compute the ordinal rank of a row within the window partition.

These functions can be used with or without partitioning and ordering. However, using them without ordering almost never makes sense.

The ranking functions can be used to create different type of counters.
Consider `SUM(1) OVER (ORDER BY SALARY)`

as an example of what they can do, each of them differently.
Following is an example query, also comparing with the `SUM`

behavior.

|`select`

|`id,`

|`salary,`

|`dense_rank() over (order by salary),`

|`rank() over (order by salary),`

|`row_number() over (order by salary),`

|`sum(1) over (order by salary)`

|`from employee`

|`order by salary;`

Results

|`id salary dense_rank rank row_number sum`

|`-- ------ ---------- ---- ---------- ---`

|`3 8.00 1 1 1 1`

|`4 9.00 2 2 2 2`

|`1 10.00 3 3 3 4`

|`5 10.00 3 3 4 4`

|`2 12.00 4 5 5 5`

The difference between `DENSE_RANK`

and `RANK`

is that there is a gap related to duplicate rows (relative to the window ordering) only in `RANK`

.
`DENSE_RANK`

continues assigning sequential numbers after the duplicate salary.
On the other hand, `ROW_NUMBER`

always assigns sequential numbers, even when there are duplicate values.

### 10.6.1. `CUME_DIST()`

Relative rank (or, cumulative distribution) of a row within a window partition

Result type`DOUBLE PRECISION`

Syntax

` |``CUME_DIST () OVER <window_name_or_spec>`

`CUME_DIST`

is calculated as the number of rows preceding or peer of the current row divided by the number of rows in the partition.

In other words, `CUME_DIST() OVER <window_name_or_spec>`

is equivalent to `COUNT(*) OVER <window_name_or_spec> / COUNT(*) OVER()`

#### 10.6.1.1. `CUME_DIST`

Examples

|`select`

|`id,`

|`salary,`

|`cume_dist() over (order by salary)`

|`from employee`

|`order by salary;`

Result

|`id salary cume_dist`

|`-- ------ ---------`

|`3 8.00 0.2`

|`4 9.00 0.4`

|`1 10.00 0.8`

|`5 10.00 0.8`

|`2 12.00 1`

### 10.6.2. `DENSE_RANK()`

See also Section 10.6.5, “`RANK()`

”, Section 10.6.4, “`PERCENT_RANK()`

”Rank of rows in a partition without gaps

Result type`BIGINT`

Syntax

` |``DENSE_RANK () OVER <window_name_or_spec>`

Rows with the same *window_order* values get the same rank within the partition *window_partition*, if specified.
The dense rank of a row is equal to the number of different rank values in the partition preceding the current row, plus one.

#### 10.6.2.1. `DENSE_RANK`

Examples

|`select`

|`id,`

|`salary,`

|`dense_rank() over (order by salary)`

|`from employee`

|`order by salary;`

Result

|`id salary dense_rank`

|`-- ------ ----------`

|`3 8.00 1`

|`4 9.00 2`

|`1 10.00 3`

|`5 10.00 3`

|`2 12.00 4`

### 10.6.3. `NTILE()`

See also Section 10.6.5, “`RANK()`

”, Section 10.6.6, “`ROW_NUMBER()`

”Distributes the rows of the current window partition into the specified number of tiles (groups)

Result type`BIGINT`

Syntax

` |``NTILE ( `*number_of_tiles* ) OVER <window_name_or_spec>

`NTILE`

Argument | Description |
---|---|

number_of_tiles | Number of tiles (groups). Restricted to a positive integer literal, a named parameter (PSQL), or a positional parameter (DSQL). |

#### 10.6.3.1. `NTILE`

Examples

|`select`

|`id,`

|`salary,`

|`rank() over (order by salary),`

|`ntile(3) over (order by salary)`

|`from employee`

|`order by salary;`

Result

|`ID SALARY RANK NTILE`

|`== ====== ==== =====`

|`3 8.00 1 1`

|`4 9.00 2 1`

|`1 10.00 3 2`

|`5 10.00 3 2`

|`2 12.00 5 3`

### 10.6.4. `PERCENT_RANK()`

Relative rank of a row within a window partition.

Result type`DOUBLE PRECISION`

Syntax

` |``PERCENT_RANK () OVER <window_name_or_spec>`

`PERCENT_RANK`

is calculated as the Section 10.6.5, “`RANK()`

” minus 1 of the current row divided by the number of rows in the partition minus 1.

In other words, `PERCENT_RANK() OVER <window_name_or_spec>`

is equivalent to `(RANK() OVER <window_name_or_spec> - 1) / CAST(COUNT(*) OVER() - 1 AS DOUBLE PRECISION)`

#### 10.6.4.1. `PERCENT_RANK`

Examples

|`select`

|`id,`

|`salary,`

|`rank() over (order by salary),`

|`percent_rank() over (order by salary)`

|`from employee`

|`order by salary;`

Result

|`id salary rank percent_rank`

|`-- ------ ---- ------------`

|`3 8.00 1 0`

|`4 9.00 2 0.25`

|`1 10.00 3 0.5`

|`5 10.00 3 0.5`

|`2 12.00 5 1`

### 10.6.5. `RANK()`

See also Section 10.6.5, “`RANK()`

”, Section 10.6.1, “`CUME_DIST()`

”Rank of each row in a partition

Result type`BIGINT`

Syntax

` |``RANK () OVER <window_name_or_spec>`

Rows with the same values of *window-order* get the same rank with in the partition *window-partition*, if specified.
The rank of a row is equal to the number of rank values in the partition preceding the current row, plus one.

#### 10.6.5.1. `RANK`

Examples

|`select`

|`id,`

|`salary,`

|`rank() over (order by salary)`

|`from employee`

|`order by salary;`

Result

|`id salary rank`

|`-- ------ ----`

|`3 8.00 1`

|`4 9.00 2`

|`1 10.00 3`

|`5 10.00 3`

|`2 12.00 5`

See alsoSection 10.6.2, “`DENSE_RANK()`

”, Section 10.6.6, “`ROW_NUMBER()`

”

### 10.6.6. `ROW_NUMBER()`

Sequential row number in the partition

Result type`BIGINT`

Syntax

` |``ROW_NUMBER () OVER <window_name_or_spec>`

Returns the sequential row number in the partition, where `1`

is the first row in each of the partitions.

#### 10.6.6.1. `ROW_NUMBER`

Examples

|`select`

|`id,`

|`salary,`

|`row_number() over (order by salary)`

|`from employee`

|`order by salary;`

Result

|`id salary rank`

|`-- ------ ----`

|`3 8.00 1`

|`4 9.00 2`

|`1 10.00 3`

|`5 10.00 4`

|`2 12.00 5`

See alsoSection 10.6.2, “`DENSE_RANK()`

”, Section 10.6.5, “`RANK()`

”