Multi-Column Sorting

Sort by Region then Quantity

Two keys sort inside first-key groups.

highlighted = computed this step

Two sort keys

ORDER BY can read one key first, then a second key inside ties.

first key, second key\text{first key, second key}

Region then quantity

The compiler uses 2 columns and leaves 1 tied group.

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

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 region then quantity: base rowsidregionqtylabel1east2a2west2b3east5c4east5d5westNULLe6north3f7eastNULLg ORDER BY keyscolumndirectionregionascqtydesc Sort keys by source rowsourceinputsortKeyorderedRank0(1, east, 2, a)(east, 2)21(2, west, 2, b)(west, 2)52(3, east, 5, c)(east, 5)03(4, east, 5, d)(east, 5)14(5, west, NULL, e)(west, NULL)65(6, north, 3, f)(north, 3)46(7, east, NULL, g)(east, NULL)3 Tie groupssortKeysourceRowstieConvention(east, 5)(2, 3)source_order Ordered rowssourcevaluessortKey2(3, east, 5, c)(east, 5)3(4, east, 5, d)(east, 5)0(1, east, 2, a)(east, 2)6(7, east, NULL, g)(east, NULL)5(6, north, 3, f)(north, 3)1(2, west, 2, b)(west, 2)4(5, west, NULL, e)(west, NULL) Page rows after LIMITsourcevaluessortKey2(3, east, 5, c)(east, 5)3(4, east, 5, d)(east, 5)0(1, east, 2, a)(east, 2)6(7, east, NULL, g)(east, NULL)5(6, north, 3, f)(north, 3)1(2, west, 2, b)(west, 2)4(5, west, NULL, e)(west, NULL) Multi-sort factsfactvaluerowCount7orderColumnCount2tieGroupCount1unresolvedTiesyesnullPlacementNULLS_LAST_TOY_CONVENTIONtieConventionsource_order_for_unbroken_tieslimitnonepageRowCount7skippedAfterLimit0outputOrderdeterministic_order_then_source_tie

Direction belongs to each key

Region is ascending while quantity is descending inside each region.

ASC then DESC\text{ASC then DESC}

Summary

Each sort key has its own direction.

per-key direction\text{per-key direction}