Multi-Column Sorting

Honest by Construction

Expected multi-column sort facts are assertions only.

highlighted = computed this step

Honest by construction

Expected multi-column sort facts are assertions only.

compiler is source of truth\text{compiler is source of truth}

Render checked rows

The guarded sort renders 3 page rows after recomputation.

checked page rows=3\text{checked page rows}=3

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.

Honest by construction: base rowsidregionqtylabel1east2a2west2b3east5c4east5d5westNULLe6north3f7eastNULLg ORDER BY keyscolumndirectionregionascqtydescidasc Sort keys by source rowsourceinputsortKeyorderedRank0(1, east, 2, a)(east, 2, 1)21(2, west, 2, b)(west, 2, 2)52(3, east, 5, c)(east, 5, 3)03(4, east, 5, d)(east, 5, 4)14(5, west, NULL, e)(west, NULL, 5)65(6, north, 3, f)(north, 3, 6)46(7, east, NULL, g)(east, NULL, 7)3 Tie groupssortKeysourceRowstieConventionnonenonenone Ordered rowssourcevaluessortKey2(3, east, 5, c)(east, 5, 3)3(4, east, 5, d)(east, 5, 4)0(1, east, 2, a)(east, 2, 1)6(7, east, NULL, g)(east, NULL, 7)5(6, north, 3, f)(north, 3, 6)1(2, west, 2, b)(west, 2, 2)4(5, west, NULL, e)(west, NULL, 5) Page rows after LIMITsourcevaluessortKey2(3, east, 5, c)(east, 5, 3)3(4, east, 5, d)(east, 5, 4)0(1, east, 2, a)(east, 2, 1) Multi-sort factsfactvaluerowCount7orderColumnCount3tieGroupCount0unresolvedTiesnonullPlacementNULLS_LAST_TOY_CONVENTIONtieConventionsource_order_for_unbroken_tieslimit3pageRowCount3skippedAfterLimit4outputOrderdeterministic_order_then_source_tie

Try to tamper

Wrong expected facts, derived sidecars, bad sort columns, duplicate sort columns, bad directions, bad limits, and mixed ordered types fail closed.

tamper rejected\text{tamper rejected}

Summary

This book shows exact finite multi-column ORDER BY and LIMIT behavior only.

scoped multi-sort model\text{scoped multi-sort model}