Window Functions

ROW_NUMBER Labels Rows

ROW_NUMBER counts rows inside each partition.

highlighted = computed this step

ROW_NUMBER labels rows

ROW_NUMBER counts visible rows inside each partition.

row number resets per partition\text{row number resets per partition}

Number each group

The compiler labels 5 rows.

numbered rows=5\text{numbered 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.

ROW_NUMBER labels rows: base rowsidregiondayamount1east152east273west144east235west26 partitionspartitionKeysourceRowsrowCount[east][0, 1, 3]3[west][2, 4]2 window rowssourceRowpartitionKeyorderKeytieBreakerKeyrowNumber0[east][1][1]11[east][2][2]23[east][2][4]32[west][1][3]14[west][2][5]2 ambiguous tiespartitionKeydeterministicKeysourceRowsnonenonenone window factsfactvaluerowCount5partitionCount2partitionColumnsregionorderColumnsdaytieBreakerColumnsidfunctionsrow_numberresultRowCount5rejectednorejectionReasonnoneambiguousTieGroups0rowOrderpartition_then_orderframenone

Read the reset

The first row of each partition starts with the first row number.

starts at one\text{starts at one}

Summary

A row number is a row label, not a collapsed summary.

one label per row\text{one label per row}