What might make sense (but is probably too messy) is to distinguish ordered tables and unordered tables in the FROM clause. Ordinary tables (and probably also virtual tables) are always unordered. VALUES is always ordered. Subqueries, views, and CTEs might be ordered or unordered. However, perhaps this distinguishing is too messy to work in all cases, and I don't know if it might break some optimizations, too. So, this is not my suggestion. Some things are too difficult to do without ordered subqueries, although such uses will always be messy with ordered subqueries anyways; rather, instead of implementing ordered and unordered subqueries like above, the need for ordered subqueries could be avoided by making improvements of SQLite, such as: - Allow `ORDER BY` in non-window aggregate functions like PostgreSQL does (e.g. "`group_concat(x, ';' order by y)`"). (You can also allow `first_value`, `last_value`, and `nth_value` to be used like any other aggregate function.) - Some way to insert row numbers into the result set after the rest of the query is handled, using the output order of the query (this can be used for both inner and outer queries, including compounds). Although the `row_number` window function could do this with the existing implementation (except for recursive CTEs; see below), the query to do this is going to be complicated; the `ORDER BY` must be duplicated, and you might have to nest compound queries, etc. The row numbers might be needed sometimes especially with recursive CTEs; I fail to see how else to return row numbers from a recursive CTE (presumably in the order that the rows are extracted from the queue). (Even, many of the examples in the documentation about `WITH` depends on the result order of subqueries, and uses the `group_concat` aggregate in the way that depends on the order, and I think that it would be better to avoid depending on that by implementing these ideas.) (Also, I had sometimes used infinite CTEs due to not knowing any other way for an extension to detect if `sqlite3_interrupt()` has been called (by something outside of the extension, such as the application code). Is there a better way?)