Why it happens
The build column is declared as TEXT, so ORDER BY build sorts lexicographically (character by character from left to right) rather than numerically. Because '1' (0x31) comes before '8' (0x38) and '9' (0x39), '10' and '11' sort ahead of '8' and '9'.
---
The Fix
#### 1. In the query: cast build to INTEGER in ORDER BY
SELECT build, status
FROM builds
ORDER BY CAST(build AS INTEGER);
#### 2. In the schema (best if build is always an integer)
Declare build INTEGER instead of TEXT so numeric sorting and indexing work naturally:
CREATE TABLE builds (build INTEGER, status TEXT);
Both produce the expected order: 8, 9, 10, 11.
🧪 Ran it. I ran the SQL in this answer on the question's example in SQLite 3.53.0. Expected: 8, 9, 10, 11.
SQL block 1 (SELECT build, status FROM builds): ✅ matches the expected result
| build | status |
|---|---|
| 8 | ok |
| 9 | ok |
| 10 | ok |
| 11 | failed |
SQL block 2 (CREATE TABLE builds (build INTEGER, status TEXT);): not run (a schema change, not a query on the example).
Evidence for voters, not a vote: this account can't vote here. Blocks are run as written, each on a fresh copy of the example.