SQLite Basics: Choose Labels and Sort Priorities with CASE

October 20, 2026

CASE chooses a result inside a query. It is useful for readable labels, ordered rules, and a business-specific sort order. The examples below keep the stored data unchanged.

Prepare a scratch directory

Tested on Linux with Python 3.12.14 and SQLite 3.53.1. You need Bash, Python 3 with SQLite support, a text editor, and mktemp, rm, and rmdir. No additional Python packages are needed. Keep the same terminal open and save files as UTF-8.

start_dir="$PWD"
work_dir="$(mktemp -d)" || exit 1
cd -- "$work_dir" || exit 1

The standalone SQLite CLI was unavailable on the test machine. Save this runner as run-sql.py. Each invocation uses a fresh in-memory database and prints pipe-separated rows, displaying missing values as NULL. These toy databases disappear when the connection closes. Put each statement’s ending semicolon at the end of a line, as shown; this is a teaching runner, not a general SQL shell or CSV exporter.

from pathlib import Path
import sqlite3
import sys

connection = sqlite3.connect(":memory:", isolation_level=None)
try:
    statement = ""
    for line in Path(sys.argv[1]).read_text(encoding="utf-8").splitlines(True):
        statement += line
        if sqlite3.complete_statement(statement):
            cursor = connection.execute(statement)
            for row in cursor:
                print("|".join("NULL" if value is None else str(value) for value in row))
            statement = ""
    if statement.strip():
        raise ValueError("Unfinished SQL statement")
finally:
    connection.close()

1. Translate known status codes

Save as status-labels.sql:

CREATE TABLE tasks (id INTEGER PRIMARY KEY, status TEXT);
INSERT INTO tasks VALUES (1, 'todo'), (2, 'doing'), (3, 'done'), (4, 'paused'), (5, NULL);
SELECT id, CASE status
    WHEN 'todo' THEN 'Not started'
    WHEN 'doing' THEN 'In progress'
    WHEN 'done' THEN 'Complete'
    ELSE 'Unknown'
END AS label
FROM tasks ORDER BY id;

Run:

python3 run-sql.py status-labels.sql

Output:

1|Not started
2|In progress
3|Complete
4|Unknown
5|Unknown

This form compares status with each WHEN value. ELSE handles both the unlisted code and NULL; WHEN NULL would not match a NULL base value. Without ELSE, unmatched rows would produce NULL.

2. Apply ordered stock rules

Save as stock-levels.sql:

CREATE TABLE stock (item TEXT, quantity INTEGER);
INSERT INTO stock VALUES ('Cable', 0), ('Dock', 4), ('Hub', 5), ('Mouse', NULL);
SELECT item, quantity, CASE
    WHEN quantity IS NULL THEN 'Check count'
    WHEN quantity = 0 THEN 'Out of stock'
    WHEN quantity < 5 THEN 'Reorder'
    ELSE 'Available'
END AS action
FROM stock ORDER BY item;

Run:

python3 run-sql.py stock-levels.sql

Output:

Cable|0|Out of stock
Dock|4|Reorder
Hub|5|Available
Mouse|NULL|Check count

The first true WHEN wins, so the zero check comes before the broader less-than-five check. The NULL check is explicit. These sample quantities are nonnegative; decide a separate rule if your data permits negative stock.

3. Sort priorities in a chosen order

Save as priority-order.sql:

CREATE TABLE tickets (id INTEGER PRIMARY KEY, priority TEXT);
INSERT INTO tickets VALUES (1, 'low'), (2, 'high'), (3, 'normal'), (4, 'high'), (5, 'other');
SELECT id, priority FROM tickets
ORDER BY CASE priority
    WHEN 'high' THEN 1
    WHEN 'normal' THEN 2
    WHEN 'low' THEN 3
    ELSE 4
END, id;

Run:

python3 run-sql.py priority-order.sql

Output:

2|high
4|high
3|normal
1|low
5|other

The numeric CASE result is used only for sorting. The second key, id, gives the two high-priority tickets a stable order. New priority codes fall into the final group until you add a rule.

See SQLite’s CASE expression reference. Use the value-comparison form for a list of codes and the condition form for ranges or NULL checks.

Clean up

In the same terminal, remove only these example files and the empty scratch directory:

rm -- run-sql.py status-labels.sql stock-levels.sql priority-order.sql
cd -- "$start_dir" || exit 1
rmdir -- "$work_dir"
unset start_dir work_dir

Acknowledgements

This post was created with assistance from AI.

Last updated on October 20, 2026

SQLite Basics: Combine and Compare Lists with Set Operations SQLite Basics: Check for Related Rows with EXISTS