Multi-Column Sorting

Base Rows before Sorting

Visible rows come before ORDER BY.

highlighted = computed this step

Base rows first

Start with visible input rows before ORDER BY changes the row order.

rows first\text{rows first}

Read the rows

The pinned table has 7 rows.

rows=7\text{rows}=7

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.

Base rows before sorting: 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

Source row stays visible

The render keeps each source row so the final order can be checked.

source row visible\text{source row visible}

Summary

Every ordered row is recomputed from these exact rows.

sort from rows\text{sort from rows}