Multi-Column Sorting

Sort by One Key

One ORDER BY key may leave ties.

highlighted = computed this step

One sort key

Sorting by region uses one visible key per row.

one ORDER BY key\text{one ORDER BY key}

Read region order

The compiler uses 1 sort column and finds 2 tied key groups.

sort columns=1\text{sort columns}=1

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.

Sort by one key: base rowsidregionqtylabel1east2a2west2b3east5c4east5d5westNULLe6north3f7eastNULLg ORDER BY keyscolumndirectionregionasc Sort keys by source rowsourceinputsortKeyorderedRank0(1, east, 2, a)(east)01(2, west, 2, b)(west)52(3, east, 5, c)(east)13(4, east, 5, d)(east)24(5, west, NULL, e)(west)65(6, north, 3, f)(north)46(7, east, NULL, g)(east)3 Tie groupssortKeysourceRowstieConvention(east)(0, 2, 3, 6)source_order(west)(1, 4)source_order Ordered rowssourcevaluessortKey0(1, east, 2, a)(east)2(3, east, 5, c)(east)3(4, east, 5, d)(east)6(7, east, NULL, g)(east)5(6, north, 3, f)(north)1(2, west, 2, b)(west)4(5, west, NULL, e)(west) Page rows after LIMITsourcevaluessortKey0(1, east, 2, a)(east)2(3, east, 5, c)(east)3(4, east, 5, d)(east)6(7, east, NULL, g)(east)5(6, north, 3, f)(north)1(2, west, 2, b)(west)4(5, west, NULL, e)(west) Multi-sort factsfactvaluerowCount7orderColumnCount1tieGroupCount2unresolvedTiesyesnullPlacementNULLS_LAST_TOY_CONVENTIONtieConventionsource_order_for_unbroken_tieslimitnonepageRowCount7skippedAfterLimit0outputOrderdeterministic_order_then_source_tie

Equal regions tie

Rows with the same region still tie until another key is added.

same key ties\text{same key ties}

Summary

One key can sort broad groups, but it may not decide every row.

ties remain\text{ties remain}