How do you use a CASE expression in ORDER BY to impose a custom priority order?

## ORDER BY sorts values, so give it the value you want sorted The sort keys of an `ORDER BY` are value expressions, not just column names. That means any expression the engine can compute per row is a legal sort key — and a `CASE` is the general way to say "sort by *this* rule" when the rule is not the natural collation of the data. The canonical use is a business priority that does not sort alphabetically. `'urgent'`, `'high'`, `'normal'`, `'low'` sorts as `high, low, normal, urgent` by text. Mapping the values to ranks fixes it: ```sql SELECT id, status, created_at FROM tickets ORDER BY CASE status WHEN 'urgent' THEN 1 WHEN 'high' THEN 2 WHEN 'normal' THEN 3 ELSE 4 END, created_at DESC; ``` The ranks exist only for the sort. They are not in the SELECT list, so nothing in the output betrays them; if you want to show the rank too, select the same expression with an alias — and then, per the standard, you may sort by that alias instead of repeating the CASE, since `ORDER BY` is the one clause where select-list aliases are visible. ## Details that decide whether it is correct **Always write an ELSE.** Without it, any status not enumerated ranks NULL, and where NULLs land in a sort is engine-dependent — the same query then orders differently on two systems. `ELSE 4` (or a deliberately large number) puts unknown values in a defined place. This also future-proofs the query: a new status added to the data lands at the end rather than in a surprising position. **Leave gaps in the ranks** if the list is likely to grow: `10, 20, 30` lets you insert a new priority later without renumbering every branch, exactly as with any ordered enumeration. **Rank values are compared as numbers, and only against each other.** Nothing requires them to be dense or to start at 1; they must only be type-compatible with one another, since the whole CASE takes a single result type. **Secondary keys still work normally.** Sort keys after the CASE break ties within each priority band, and `ASC`/`DESC` applies to the key it directly follows — `created_at DESC` above descends `created_at` only, not the CASE rank. ## The two other common shapes **Pinning a group to the top or bottom.** A boolean-shaped CASE is enough: ```sql ORDER BY CASE WHEN status = 'urgent' THEN 0 ELSE 1 END, created_at DESC ``` Everything urgent floats up; the rest keep their natural order. **Controlling where missing values land, portably.** `ORDER BY CASE WHEN due_date IS NULL THEN 1 ELSE 0 END, due_date` pushes rows with no due date last regardless of the engine's default null placement — useful in code that must run on more than one system. ## What CASE cannot do here `ASC` and `DESC` are **syntax**, not values, so no expression can produce them: `ORDER BY CASE WHEN @dir = 'd' THEN 'DESC' ELSE 'ASC' END` sorts every row by a constant string and effectively does nothing. If a caller must choose the direction, the honest options are two conditional keys — ```sql ORDER BY CASE WHEN :dir = 'asc' THEN created_at END ASC, CASE WHEN :dir = 'desc' THEN created_at END DESC ``` — where the unselected key is NULL for every row and therefore does not discriminate, or choosing between two fixed query texts in the application. If you go the second route, the column and direction must come from a fixed allowlist rather than from caller-supplied text. Similarly, a CASE cannot make the *number* of sort keys dynamic, and it cannot sort by a column the query has not selected from — it is an expression over the row, nothing more. ## Reading a CASE sort key in review When you meet one in someone else's query, check three things: is there an `ELSE` (so no rank is NULL); are the enumerated values still the values in the data (a shadowed or obsolete branch silently reorders results); and does the tie-break key make the order deterministic? Without a final unique tie-break, rows sharing a priority may come back in a different sequence between runs, and a paginated screen built on that ordering will show duplicates or gaps.