Window Functions

RANK and DENSE_RANK

Rank functions label peer rows from the order key.

highlighted = computed this step

RANK and DENSE_RANK

RANK and DENSE_RANK use the ORDER BY key to find peers.

same order key means peer rows\text{same order key means peer rows}

Compare rank labels

The rank table still has 5 rows.

ranked rows=5\text{ranked rows}=5

Window-function examples are tiny finite table transforms; SQL dialects, NULL ordering, frames beyond prefix rows, optimizer behavior, performance, and product behavior are out of scope.

RANK and DENSE_RANK: base rowsidregiondayamount1east152east273west144east235west26 partitionspartitionKeysourceRowsrowCount[east][0, 1, 3]3[west][2, 4]2 window rowssourceRowpartitionKeyorderKeytieBreakerKeyrankdenseRank0[east][1][1]111[east][2][2]223[east][2][4]222[west][1][3]114[west][2][5]22 ambiguous tiespartitionKeydeterministicKeysourceRowsnonenonenone window factsfactvaluerowCount5partitionCount2partitionColumnsregionorderColumnsdaytieBreakerColumnsidfunctionsrank,dense_rankresultRowCount5rejectednorejectionReasonnoneambiguousTieGroups0rowOrderpartition_then_orderframenone

Read peers

Rows with the same order key share a rank; dense rank closes the gap.

peer rows share labels\text{peer rows share labels}

Summary

The tie-breaker fixes display order but the rank peer key remains the ORDER BY key.

peer key is separate\text{peer key is separate}