Multi-Column Sorting

LIMIT after Sorting

LIMIT keeps a prefix after sorting.

highlighted = computed this step

LIMIT after sorting

LIMIT keeps a prefix after the ORDER BY result exists.

sort first, then limit\text{sort first, then limit}

Read the page

The page keeps 3 rows and skips 4 later rows.

page rows=3\text{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.

LIMIT after sorting: 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

Ordered rows still visible

The ordered rows table shows the full order before the shorter page.

prefix after order\text{prefix after order}

Summary

LIMIT cuts a deterministic prefix from the sorted rows.

sorted prefix\text{sorted prefix}