SQLite Basics: Select, Filter, and Order Rows

A useful query answers a specific question: which tasks need attention, which values are missing, or which items belong at the top of a list? Start with a small input and make the output columns and ordering explicit.

These examples use a WITH clause to name a tiny, temporary result made with VALUES. Think of it as an inline practice table available only to the following statement. There is no persistent database to prepare.

Prepare a scratch directory

Tested on Linux with Python 3.12.14 and SQLite 3.53.1. No packages are needed. You need Bash, Python 3 with SQLite support, a text editor, and mktemp, rm, and rmdir. 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 small runner as run-sql.py. It executes SQL against a fresh in-memory database and prints pipe-separated rows, displaying missing values as NULL. Put each statement’s ending semicolon at the end of its own line, as below; 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. Choose rows and calculated columns

Save as filter-tasks.sql:

WITH tasks(id, title, minutes, status) AS (
  VALUES (1, 'Outline', 20, 'open'),
         (2, 'Proofread', 45, 'open'),
         (3, 'Publish', 10, 'done'),
         (4, 'Check links', 30, 'open')
)
SELECT title, minutes, minutes / 60.0 AS hours
FROM tasks
WHERE status = 'open' AND minutes >= 30
ORDER BY minutes, id;

Run:

python3 run-sql.py filter-tasks.sql

Output:

Check links|30|0.5
Proofread|45|0.75

SELECT chooses the displayed columns. WHERE keeps rows satisfying both conditions. The expression named hours uses 60.0 so the division produces a fractional result; dividing two integers would discard the fractional part.

Writing the columns explicitly also makes the report easier to review when the input gains more fields. Here, the status helps select rows without appearing in the output.

2. Handle missing values deliberately

Save as missing-owner.sql:

WITH tasks(id, title, owner) AS (
  VALUES (1, 'Outline', 'Mina'),
         (2, 'Proofread', NULL),
         (3, 'Publish', '')
)
SELECT title, COALESCE(owner, 'Unassigned') AS owner_label
FROM tasks
WHERE owner IS NULL
ORDER BY id;

Run:

python3 run-sql.py missing-owner.sql

Output:

Proofread|Unassigned

NULL represents a missing value. Use IS NULL to find it. An empty string is a present string with no characters, so the Publish row does not match this condition. Decide whether your application should treat those states differently before writing its filters.

COALESCE supplies a display label here. It does not update the stored value. Avoid replacing missing values before filtering unless you actually want to change the meaning of the comparison.

3. Choose a stable top two

Save as top-priorities.sql:

WITH tasks(id, title, priority) AS (
  VALUES (1, 'Outline', 3),
         (2, 'Proofread', 5),
         (3, 'Publish', 5),
         (4, 'Check links', 2)
)
SELECT id, title, priority
FROM tasks
ORDER BY priority DESC, id ASC
LIMIT 2;

Run:

python3 run-sql.py top-priorities.sql

Output:

2|Proofread|5
3|Publish|5

The highest priority comes first because of DESC. The unique ID breaks equal-priority ties, giving the same rows a defined order. LIMIT 2 then bounds the result. Without an explicit order, a query does not promise which rows appear first.

This pattern is useful for a small work queue. A limit is a maximum, not a guarantee: when fewer than two rows qualify, fewer rows are returned. Add a filter before ordering if the list should include only unfinished tasks.

For the clause definitions, see SQLite’s official SELECT documentation. When moving these examples into an application, bind user input as parameters rather than concatenating it into SQL.

Clean up

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

rm -- run-sql.py filter-tasks.sql missing-owner.sql top-priorities.sql
cd -- "$start_dir" || exit 1
rmdir -- "$work_dir"
unset start_dir work_dir

Acknowledgements

This post was created with assistance from AI.