Ordering

Add a Tie Breaker

A second key makes tied rows exact.

highlighted = computed this step

Add a tie breaker

When scores tie, a second column can make the order exact.

score then id\text{score then id}

Use two keys

The order uses 2 columns, and unstableTies is no.

order columns=2\text{order columns}=2

Ordering examples are tiny finite table transforms; SQL dialects, NULL ordering, collations, indexes, optimizer behavior, performance, and product behavior are out of scope.

Add a tie breaker: base tablesourceRowidkindscore01book9012game7523book7534game9045book90 order bycolumndirectionscoredescidasc sort keyssourceRowkey0[90, 1]1[75, 2]2[75, 3]3[90, 4]4[90, 5] ordered rowssourceRowidkindscore01book9034game9045book9012game7523book75 page rowssourceRowidkindscore01book9034game9045book9012game7523book75 tie groupskeysourceRowsnonenone order factsfactvaluerowCount5orderColumnCount2orderedRowCount5offset0limitnonepageRowCount5skippedBeforeCount0skippedAfterCount0unstableTiesnodeterministicTieOrdernot_needed

Read the two-part key

Each sort key has score first and id second.

tie breaker is visible\text{tie breaker is visible}

Summary

The compiler does not rely on a hidden row order when a tie breaker is present.

explicit tie breaker\text{explicit tie breaker}