Why is nine sorted ahead of ten in a quantity export?

Prepare the report input

The fictional source exports canonical whole quantity text in the inclusive range 0..9223372036854775807: 2,10,9. Text sorting compares the strings, not their quantities. Cast only this declared numeric quantity in ORDER BY, keeping the source text in the result.

Every record and report rule below is original and fictional. Reproduce the setup in a fresh SQLite test database; input previews include every table in this small fixture.

CREATE TABLE quantities(id TEXT PRIMARY KEY,units_text TEXT NOT NULL); INSERT INTO quantities VALUES('A','2'),('B','10'),('C','9');

Compare misleading and corrected results

SELECT id,units_text FROM quantities ORDER BY units_text DESC,id;

SELECT id,units_text FROM quantities ORDER BY CAST(units_text AS INTEGER) DESC,id;

Descending text order is 9,2,10; numeric order is 10,9,2. The sort cast applies only to the declared units_text quantity. Invalid text and quantities above SQLite’s signed integer maximum must be rejected before this query; CAST can clamp overflow rather than preserve its order.

Check a fresh boundary case

Add zero and the two largest permitted quantities, 9223372036854775807 and 9223372036854775806. Their distinct numeric order remains intact; zero sorts last.

Recreate the original setup in another fresh database, then apply the change and corrected query below. For an empty result, the table displays a labelled empty-result marker rather than a data record.

INSERT INTO quantities VALUES('D','0'),('E','9223372036854775807'),('F','9223372036854775806');
SELECT id,units_text FROM quantities ORDER BY CAST(units_text AS INTEGER) DESC,id;