11.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.

11.6.1. CUME_DIST()

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

Result typeDOUBLE 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()

11.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

11.6.2. DENSE_RANK()

See also Section 11.6.5, “RANK()”, Section 11.6.4, “PERCENT_RANK()”Rank of rows in a partition without gaps

Result typeBIGINT

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.

11.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

11.6.3. NTILE()

See also Section 11.6.5, “RANK()”, Section 11.6.6, “ROW_NUMBER()”Distributes the rows of the current window partition into the specified number of tiles (groups)

Result typeBIGINT

Syntax 

NTILE ( number_of_tiles ) OVER <window_name_or_spec>

Table 11.2. Arguments of NTILE
ArgumentDescription

number_of_tiles

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

11.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

11.6.4. PERCENT_RANK()

Relative rank of a row within a window partition.

Result typeDOUBLE PRECISION

Syntax 

PERCENT_RANK () OVER <window_name_or_spec>

PERCENT_RANK is calculated as the Section 11.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)

11.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

11.6.5. RANK()

See also Section 11.6.5, “RANK()”, Section 11.6.1, “CUME_DIST()”Rank of each row in a partition

Result typeBIGINT

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.

11.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 11.6.2, “DENSE_RANK()”, Section 11.6.6, “ROW_NUMBER()”

11.6.6. ROW_NUMBER()

Sequential row number in the partition

Result typeBIGINT

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.

11.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 11.6.2, “DENSE_RANK()”, Section 11.6.5, “RANK()”