Sorting and Limiting Results

ORDER BY & LIMIT

Filtering and joining rows only gets you an unordered result set — clients almost always want output in a specific order, and often only the top handful of rows. This lesson adds ORDER BY and LIMIT, plus a planner optimization: skip sorting entirely when an index already provides the right order.

Basic sorting

SELECT * FROM users ORDER BY age ASC
SELECT * FROM users ORDER BY name DESC LIMIT 5
SELECT name FROM users ORDER BY age LIMIT 3

The mechanics are a straightforward comparator sort over the result rows:

Default direction is ASC when neither ASC nor DESC is written. Note the order of operations: you sort the full result set first, then truncate with LIMIT — truncating before sorting would give you an arbitrary subset instead of the top-N by the requested order.

Parsing considerations

ORDER BY and LIMIT are clauses that stack on top of whatever WHERE/projection logic you already have, so parse them as optional trailing clauses:

SELECT <cols> FROM <table> [WHERE ...] [ORDER BY <col> [ASC|DESC]] [LIMIT <n>]

Handle each clause independently — a query can have ORDER BY without LIMIT, LIMIT without ORDER BY (less meaningful, since without a defined order "first N rows" is arbitrary, but still legal), or neither.

The optimization: index-provided order

Here's where this lesson connects back to the query-planner chapter. A B-tree index stores its keys in sorted order — that's the whole point of a tree-based index. If a table has an index on the exact column named in ORDER BY, you can walk the index leaves in order and never need an explicit sort step at all:

Naive:   read all rows -> sort by age -> return
Indexed: walk idx_age leaves left-to-right (or right-to-left for DESC) -> return

This is a real technique — it's why EXPLAIN in Postgres sometimes shows an Index Scan even for a query with no WHERE clause at all, purely to satisfy an ORDER BY. It also composes beautifully with LIMIT: if you're walking an already-sorted index and you only need the first 3 rows, you can stop as soon as you have 3 rather than sorting the entire table and slicing — turning an O(n log n) sort into effectively O(limit) work.

Implementation sketch for the index path

1. Check whether an index exists on the ORDER BY column.
2. If yes: iterate the index's key-sorted entries (reverse the iteration for DESC), resolve each to its row, apply any WHERE filter, and stop early once LIMIT rows have been collected.
3. If no index: fall back to "materialize matching rows, sort in memory, then slice by LIMIT."

Edge cases to watch

• LIMIT larger than the result set size should just return everything, not error.
• Sorting mixed types (e.g. a text column with "10" and "9") — decide whether comparison is numeric or lexicographic and be consistent with how the rest of your engine stores values.
• Ties in the sort column: without a documented tie-breaker, don't assume a specific secondary order — but if your test suite expects insertion-order stability on ties, use a stable sort (Python's sort/sorted already are stable, which helps here for free).
• Selecting a subset of columns (SELECT name FROM users ORDER BY age) still needs to sort by age even though age isn't in the output — sort using the full row, then project down to the requested columns afterward.