10.4. Ranking Functions

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

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

The ranking functions can be used to create different type of incremental counters. Consider SUM(1) OVER (ORDER BY SALARY) as an example of what they can do, each of them in a different way. 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.4.1. DENSE_RANK

Available inDSQL, PSQL

Result typeBIGINT

Syntax 

  |DENSE_RANK () OVER <window-specification>

Returns the rank of rows in a partition of a result set without ranking gaps. 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.4.1.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.4.2. RANK

Available inDSQL, PSQL

Result typeBIGINT

Syntax 

  |RANK () OVER <window-specification>

Returns the rank of each row in a partition of the result set. 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.4.2.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.4.1, “DENSE_RANK”, Section 10.4.3, “ROW_NUMBER”

10.4.3. ROW_NUMBER

Available inDSQL, PSQL

Result typeBIGINT

Syntax 

  |ROW_NUMBER () OVER <window-specification>

Returns the sequential row number in the partition of the result set, where 1 is the first row in each of the partitions.

10.4.3.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.4.1, “DENSE_RANK”, Section 10.4.2, “RANK”