22 Sep 19 · npack · #Bigquery ·   Bookmark   ×

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.

npack

posted on 22 Sep 19

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds

Copied