Multi-Column Sorting

Add a Tie Breaker

An extra key makes tied rows explicit.

highlighted = computed this step

Add a tie breaker

A second key such as id can decide rows with equal quantities.

qty then id\text{qty then id}

Quantity then id

The compiler uses 2 sort columns and leaves 0 unresolved tie groups.

unresolved ties=0\text{unresolved ties}=0

Multi-column sorting examples are tiny finite table transforms; SQL dialect completeness, collation rules, optimizer behavior, indexing, product behavior, and performance claims are out of scope.

Add a tie breaker: base rowsidregionqtylabel1east2a2west2b3east5c4east5d5westNULLe6north3f7eastNULLg ORDER BY keyscolumndirectionqtyascidasc Sort keys by source rowsourceinputsortKeyorderedRank0(1, east, 2, a)(2, 1)01(2, west, 2, b)(2, 2)12(3, east, 5, c)(5, 3)33(4, east, 5, d)(5, 4)44(5, west, NULL, e)(NULL, 5)55(6, north, 3, f)(3, 6)26(7, east, NULL, g)(NULL, 7)6 Tie groupssortKeysourceRowstieConventionnonenonenone Ordered rowssourcevaluessortKey0(1, east, 2, a)(2, 1)1(2, west, 2, b)(2, 2)5(6, north, 3, f)(3, 6)2(3, east, 5, c)(5, 3)3(4, east, 5, d)(5, 4)4(5, west, NULL, e)(NULL, 5)6(7, east, NULL, g)(NULL, 7) Page rows after LIMITsourcevaluessortKey0(1, east, 2, a)(2, 1)1(2, west, 2, b)(2, 2)5(6, north, 3, f)(3, 6)2(3, east, 5, c)(5, 3)3(4, east, 5, d)(5, 4)4(5, west, NULL, e)(NULL, 5)6(7, east, NULL, g)(NULL, 7) Multi-sort factsfactvaluerowCount7orderColumnCount2tieGroupCount0unresolvedTiesnonullPlacementNULLS_LAST_TOY_CONVENTIONtieConventionsource_order_for_unbroken_tieslimitnonepageRowCount7skippedAfterLimit0outputOrderdeterministic_order_then_source_tie

Tie breaker is explicit

The id key is visible in the ORDER BY table.

id tie breaker\text{id tie breaker}

Summary

A tie breaker turns source-order convention into an authored rule.

authored order\text{authored order}