Sort Rows By A Custom Expression

Use a `CASE` expression in `ORDER BY` to apply a custom sort order that is not alphabetical or numeric.

Created Apr 26, 2026 Last updated Apr 26, 2026 5/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Show in-progress tasks first, then open, then closed

The CASE expression maps each status string to a numeric priority: in_progress → 1, open → 2, closed → 3. The ORDER BY sorts by that number, then by id as a tiebreaker so that the two open tasks appear in a stable order. The CASE result does not appear in the output columns.

Source table data Rows loaded before the example query runs.

Setup

CREATE TABLE tasks (id INT, name VARCHAR(50), status VARCHAR(20));

INSERT INTO
  tasks
VALUES
  (1, 'Fix bug', 'open'),
  (2, 'Write docs', 'closed'),
  (3, 'Deploy', 'in_progress'),
  (4, 'Review', 'open');

Validated query Shared across supported engines.

SQL

SELECT
  id,
  name,
  status
FROM
  tasks
ORDER BY
  CASE status
    WHEN 'in_progress' THEN 1
    WHEN 'open' THEN 2
    WHEN 'closed' THEN 3
    ELSE 4
  END,
  id;

Expected result Returned rows for the shared example.

id	name	status
3	Deploy	in_progress
1	Fix bug	open
4	Review	open
2	Write docs	closed

Identical syntax and result across all engines.

Useful when

Where this command helps.

displaying work items with active statuses at the top of a list
sorting rows by a priority or severity level stored as text

Explanation

What the command is doing.

Sometimes rows need to be sorted by a business rule rather than a raw column value — for example, showing in-progress tasks before open ones and closed ones last. Placing a CASE expression directly in ORDER BY defines a numeric rank for each row without adding an extra column to the result. The expression is computed at sort time and discarded. This pattern works across all major databases.

Sort Rows By A Custom Expression

Show in-progress tasks first, then open, then closed

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.