Can ORDER BY sort by an expression or by a select-list position such as ORDER BY 2?

## Three shapes a sort key can take ORDER BY accepts more than bare column names. In practice you will see three forms: 1. **A column reference** — `ORDER BY created_at`. 2. **An expression** — `ORDER BY CHAR_LENGTH(title) DESC`, `ORDER BY unit_price * quantity DESC`, `ORDER BY CAST(version_text AS INTEGER)`. 3. **An ordinal position** — `ORDER BY 2`, meaning the second item of the select list. All three can be mixed in one list and each takes its own ASC/DESC. ## Sorting by an expression you do not return Because ordering happens over the rows the query has already produced, the engine can compute a sort key that never reaches the client: ```sql SELECT product_name FROM products ORDER BY unit_price * stock_quantity DESC; ``` The result has one column, but the rows come back in inventory-value order. This is genuinely useful: the sort criterion is often a derived quantity the consumer does not need to see. The exception is `SELECT DISTINCT`. Duplicate elimination collapses rows before ordering, so a sort key that is not part of the returned row is ambiguous — the surviving row would have several possible values for it — and the standard requires ORDER BY items to be select-list items in that case. `SELECT DISTINCT city FROM users ORDER BY created_at` is not well-defined and engines reject it. ## Expressions that fix a wrong-looking sort A common interview follow-up is the column of numbers stored as text. `ORDER BY version_text` sorts lexicographically, so `'10'` lands before `'9'` — string comparison examines characters left to right and `'1'` precedes `'9'`. The fix is an expression key: ```sql ORDER BY CAST(version_text AS INTEGER); ``` The same shape solves date-as-text columns and mixed-case sorting. The point is that the *type of the sort key expression*, not the type of the stored column, decides how values are compared. ## Ordinal positions: what the integer means An unadorned integer in ORDER BY is not a value to sort by — it is a reference to the select list by position, counting from 1: ```sql SELECT category, SUM(amount) AS total FROM sales GROUP BY category ORDER BY 2 DESC; -- by total, descending ``` That is why `ORDER BY 2` is not the same as `ORDER BY 'a constant'`: a non-integer literal is just a constant expression with the same value in every row, so it imposes no ordering at all — a silent no-op that occasionally shows up in generated SQL. Ordinals are widely accepted and handy at a prompt, especially for sorting by an aggregate or a long expression you do not want to retype. In code that lives, they are a liability: adding one column to the front of the select list shifts every position, the query still parses, and the output is quietly sorted by the wrong thing. Prefer naming the column or repeating the expression. ## Aliases as sort keys You can also sort by a column alias defined in the select list — `SELECT SUM(amount) AS total ... ORDER BY total DESC` — because ORDER BY is the one clause positioned after the select list is computed. Watch for a name collision: if the alias reuses a base-table column name, an unqualified reference in ORDER BY is ambiguous between the output column and the underlying column, and engines resolve that differently. Avoid the collision rather than memorise the resolution rules. ## Combining forms Nothing stops you mixing them, though readability suffers: ```sql SELECT customer_id, COUNT(*) AS order_count, MAX(placed_at) AS last_order FROM orders GROUP BY customer_id ORDER BY order_count DESC, last_order DESC, customer_id; ``` Here two aliases and one column carry the ordering. The same query written `ORDER BY 2 DESC, 3 DESC, 1` behaves identically and reads far worse. ## Practical guidance - Use expressions freely as sort keys; they do not have to be selected (except under DISTINCT). - Reach for CAST when a text column needs numeric or chronological ordering. - Treat ordinals as an interactive convenience only, never in views, reports or application code. - Remember that a non-integer literal sort key sorts nothing. - Do not let a select-list alias shadow a base column name you also sort by.