Recipe

Custom sort order with CASE

Short answer: ORDER BY CASE col WHEN 'high' THEN 1 WHEN 'medium' THEN 2 WHEN 'low' THEN 3 END lets you define an arbitrary sort order for non-alphabetical categories: priority levels, status workflows, custom rankings. Standard SQL, works on every engine. The CASE returns a sort key that the engine then orders normally.

Canonical SQL #

sql

-- Single CASE expression in ORDER BY.
WITH tickets(id, priority, created_at) AS (
  VALUES
    (1, 'low',      TIMESTAMP '2026-04-01 09:00'),
    (2, 'critical', TIMESTAMP '2026-04-01 10:00'),
    (3, 'medium',   TIMESTAMP '2026-04-02 08:30'),
    (4, 'high',     TIMESTAMP '2026-04-02 11:15')
)
SELECT *
FROM   tickets
ORDER  BY
  CASE priority
    WHEN 'critical' THEN 1
    WHEN 'high'     THEN 2
    WHEN 'medium'   THEN 3
    WHEN 'low'      THEN 4
    ELSE                 5         -- catch-all for unexpected values
  END,
  created_at DESC;                 -- secondary sort within same priority

Example #

Loading SQL editor...

Common patterns #

sql

-- Custom status ordering (workflow stage order).
SELECT *
FROM   orders
ORDER  BY
  CASE status
    WHEN 'pending'   THEN 1
    WHEN 'paid'      THEN 2
    WHEN 'shipped'   THEN 3
    WHEN 'delivered' THEN 4
    WHEN 'cancelled' THEN 99
    WHEN 'refunded'  THEN 99
    ELSE                  98
  END;

-- "Pinned items first" pattern.
WITH articles(id, title, is_pinned, published_at) AS (
  VALUES
    (1, 'Q1 results',   FALSE, DATE '2026-03-01'),
    (2, 'Welcome post', TRUE,  DATE '2025-01-15'),
    (3, 'Changelog',    FALSE, DATE '2026-04-10')
)
SELECT *
FROM   articles
ORDER  BY
  CASE WHEN is_pinned THEN 0 ELSE 1 END,    -- pinned first
  published_at DESC;

-- Alphabetical, but with one item forced to top.
WITH countries(code, name) AS (
  VALUES ('BR', 'Brazil'), ('DE', 'Germany'), ('US', 'United States')
)
SELECT *
FROM   countries
ORDER  BY
  CASE WHEN code = 'US' THEN 0 ELSE 1 END,
  name;

-- Distance-based custom sort: closest tier first.
SELECT *
FROM   products
ORDER  BY
  CASE
    WHEN price < 50               THEN 1
    WHEN price < 100              THEN 2
    WHEN price < 500              THEN 3
    ELSE                              4
  END,
  name;

-- Sort by a list (alternative pattern with a join).
WITH tickets(id, priority, title) AS (
  VALUES
    (1, 'low',      'doc typo'),
    (2, 'critical', 'prod down'),
    (3, 'medium',   'add filter'),
    (4, 'high',     'data missing')
)
SELECT t.*
FROM   tickets t
JOIN   (VALUES ('critical', 1), ('high', 2), ('medium', 3), ('low', 4))
       AS rank_order(priority, rank)
  ON   rank_order.priority = t.priority
ORDER  BY rank_order.rank;

Common mistakes #

No ELSE branch: rows with unexpected values get NULL sort keys, which fall to the end (or start, depending on engine default). Add an ELSE to control where they land.
Sort key collisions: if two priorities map to the same number ('cancelled' THEN 99 and 'refunded' THEN 99), the order between them is non-deterministic. Add a tie-breaker column (ORDER BY CASE..., id).
Repeating the CASE in SELECT and ORDER BY: write it once with an alias if your engine supports referencing aliases in ORDER BY (most do).
Performance on huge tables: a CASE in ORDER BY evaluates per row. For frequently-queried sorts, a priority_rank lookup column or table is faster.

Dialect notes #

ORDER BY CASE is standard ANSI SQL — works on every engine.
MySQL has FIELD(): ORDER BY FIELD(priority, 'critical', 'high', 'medium', 'low') is shorter syntax for the same operation. Returns the position of priority in the list (or 0 if not found, which sorts to the top).
Postgres / DuckDB: no FIELD, but you can use array position: ORDER BY array_position(ARRAY['critical','high','medium','low'], priority).
**For sorts that change over time** (today's "priority order" is different from last quarter's), store the rank in a real table and JOIN to it — keeps the rule centralized.

Tip

For any custom-ordered category that appears in more than 2-3 places, store the order in a lookup table. A priority_levels table with (priority, sort_rank) rows, joined into queries, beats copy-pasting the same CASE block across 30 dashboards. When the product team adds "blocker" between "critical" and "high," you change one row, not 30 queries. The same pattern applies to status workflows, severity levels, and any business taxonomy that has a meaningful order. Detail: CASE expressions.