SQLite Forum

Is the order conserved in a table from a VALUES clause?
Login
First, it's just wrong to assume rows come in any particular order,  
unless you explicitly ask for a given order, via an `ORDER BY` clause.

The order of rows depends on the query plan, whether it uses an index or not,  
and even whether some pragmas are used or not.

Second, if you have your Primary Key match the internal SQLite *rowid*, as you do,  
and explicitly provide those PKs are you do in your example, then simply use  
`select val, txt from x ORDER BY val` to have a deterministic order  
*for free*, since the FULL SCAN will be in PK-order, no need for Window functions.

SQLite will never provide any guarantees for what order rows are **stored** into,  
nor how they are selected into (w/o an `order by` clause), whether you insert them  
with `values()` or not. How it works now is not relevant either, as that could change too.