SQL ORDER BY CASE: Priorities, Typed Choices, NULLs, and Ties

ORDER BY with CASE: make every priority and tie explicit

CASE in ORDER BY supplies a sort value. It does not create a special sorting algorithm or guarantee a useful order among ties. This PostgreSQL 16 lesson sorts a work queue by business priority, handles unknown values deliberately, and offers a typed choice between date and amount ordering. Each fixture is complete and read-only.

Define the queue before writing CASE

Urgent tasks come first, normal tasks next, and unrecognized or missing statuses last. Within a priority, known due dates come before missing dates, earlier dates come first, and id breaks any remaining tie. These are example business rules; do not assume a NULL status means normal. The unique id makes repeated executions deterministic for the same input snapshot.

Assign a numeric priority, then sort within it

WITH tasks(id,status,due_on,amount) AS (
 VALUES(1,'urgent',DATE '2026-01-02',20::numeric),
 (2,'normal',DATE '2026-01-01',10),(3,'urgent',NULL,5),
 (4,'urgent',DATE '2026-01-02',20),(5,NULL,DATE '2026-01-01',7),
 (6,'other',DATE '2026-01-01',NULL)
)
SELECT id,status,due_on,
 CASE status WHEN 'urgent' THEN 1 WHEN 'normal' THEN 2 ELSE 3 END AS priority
FROM tasks
ORDER BY priority,due_on ASC NULLS LAST,id;

id | status | due_on | priority
1 | urgent | 2026-01-02 | 1
4 | urgent | 2026-01-02 | 1
3 | urgent | NULL | 1
2 | normal | 2026-01-01 | 2
5 | NULL | 2026-01-01 | 3
6 | other | 2026-01-01 | 3

Task 3 stays ahead of normal task 2 because status priority precedes the due-date key. Tasks 1 and 4 have the same priority and date, so id selects their order. ELSE explicitly groups unexpected and NULL statuses at the end; they are still distinguishable in the selected status column.

Use separate typed expressions for selectable sorting

A single CASE that sometimes returns a date, sometimes a number, and sometimes text must still resolve to one output type. It does not postpone type checking until the selected branch runs. Keep each candidate sort key in its own CASE and validate the option against a documented allowlist in the application.

WITH tasks(id,status,due_on,amount) AS (
 VALUES(1,'urgent',DATE '2026-01-02',20::numeric),
 (2,'normal',DATE '2026-01-01',10),(3,'urgent',NULL,5),
 (4,'urgent',DATE '2026-01-02',20),(5,NULL,DATE '2026-01-01',7),
 (6,'other',DATE '2026-01-01',NULL)
)
, params(sort_key) AS (VALUES('amount'))
SELECT t.id,t.due_on,t.amount FROM tasks t CROSS JOIN params p
ORDER BY
 CASE WHEN p.sort_key='date' THEN t.due_on END ASC NULLS LAST,
 CASE WHEN p.sort_key='amount' THEN t.amount END DESC NULLS LAST,
 t.id;

id | due_on | amount
1 | 2026-01-02 | 20
4 | 2026-01-02 | 20
2 | 2026-01-01 | 10
5 | 2026-01-01 | 7
3 | NULL | 5
6 | 2026-01-01 | NULL

Here amount is selected, so the date CASE yields NULL for every row and does not distinguish them. The numeric CASE orders known amounts descending, followed by NULL, then id. With date selected, the expected id order is 2, 5, 6, 1, 4, 3. An invalid option would make both CASE expressions NULL and fall back to id; the application should reject such an option rather than silently presenting a different sort.

Bind the selected option as a value through the driver. A parameter is not a column identifier or the ASC/DESC keyword. Do not concatenate arbitrary user text into ORDER BY. If a query builder generates distinct SQL for allowed sort choices, map an allowlist of choices to fixed trusted expressions and keep values parameterized.

Different directions require explicit group and value keys

WITH prices(id,price) AS (
 VALUES(1,3::numeric),(2,8),(3,12),(4,20),(5,NULL)
)
SELECT id,price FROM prices
ORDER BY CASE WHEN price<10 THEN 1 WHEN price>=10 THEN 2 ELSE 3 END,
 CASE WHEN price<10 THEN price END ASC NULLS LAST,
 CASE WHEN price>=10 THEN price END DESC NULLS LAST,id;

id | price
1 | 3
2 | 8
4 | 20
3 | 12
5 | NULL

The first expression fixes the group order: cheap, expensive, unknown. The next two independently choose ascending or descending values within their relevant group. This avoids relying on dialect-specific default NULL placement. NULLS LAST is supported in PostgreSQL; in another engine, use its syntax or an explicit CASE null-flag with equivalent tests.

Explain the sort as a list of rules

Describe the first key, the within-group key, the NULL rule, and the final unique tie-breaker. Then test a missing value and a tie at every level. If pagination is required, use the same complete ordering for page boundaries and define what happens when records change between requests; an id tie-breaker alone does not freeze a changing dataset.

Measure expression sorting instead of promising an index win

A custom CASE order may require computing keys and sorting the candidate rows. An ordinary view stores a query definition, not precomputed sorted rows. An expression index or generated column can sometimes help a stable workload, but it has write/storage costs and must match the expression and filtering needs. Compare plans and representative measurements before adding one.

The best initial optimization can be reducing the authorized candidate population before sorting, provided the filter does not change the requested result. Avoid “always consolidate all CASE expressions” advice: separate expressions are necessary when directions or result types differ. Readable, typed keys are easier to verify than a compact coercion-heavy expression.

Check the exact sequence

The priority example yields ids 1, 4, 3, 2, 5, 6. The amount option yields 1, 4, 2, 5, 3, 6. The mixed-direction prices yield 1, 2, 4, 3, 5. Those ordered lists are stronger tests than checking that the query merely runs. Primary references: ORDER BY, CASE, and type resolution.

FAQ

Does CASE need ELSE? Omitting it returns NULL when no branch matches; that is intentional for the selectable keys but not the priority classification. Are NULLs always last? Defaults vary by engine and direction; specify the policy. Does CASE modify stored data? These SELECT expressions only calculate output and sorting keys.