Rank vs Dense_Rank in Bigquery - Syntax

Numbering Functions

Numbering functions assign integer values to each row based on their position within the specified window.

Example of RANK(), DENSE_RANK(), and ROW_NUMBER():

SELECT x,
  RANK() OVER (ORDER BY x ASC) AS rank,
  DENSE_RANK() OVER (ORDER BY x ASC) AS dense_rank,
  ROW_NUMBER() OVER (PARTITION BY x ORDER BY y) AS row_num
FROM ...

x	rank	dense_rank    row_num
1	1	1	1
2	2	2	2
2	2	2	3
5	4	3	4
8	5	4	5
10	6	5	6
10	6	5	7

• RANK():For x=5, rank returns 4, since RANK() increments by the number of peers in the previous window ordering group.
• DENSE_RANK(): For x=5, dense_rank returns 3, since DENSE_RANK() always increments by 1, never skipping a value.
• ROW_NUMBER():For x=5, row_num returns 4.

nVector

posted on 22 Sep 19