2017-08-19 22:03 GMT+02:00 Keith Medcalf <[email protected]>:
>
> When you run the second query, the optimizer does not know that the data
> returned from the view is already sorted into the "correct" order and the
> inclusion of the "order by" in the view prevents the query flattener from
> optimizing out (re-writing) the query into a single query (eliminating the
> view). Without flattening, the view is simply a "black box" that returns
> some rows in an arbitrary (completely unsorted) order.
>
> If you remove the "order by" from the view, then the query flattener will
> be able to flatten the query and re-write and execute as if you had entered:
>
> SELECT used
> FROM proverbs
> WHERE CAST(used AS INT) <> 0
> ORDER BY used
> LIMIT 1
>
​Thanks. I learned something.
By the way, I should have used:
SELECT MIN(used)
FROM usedProverbs
--
Cecil Westerhof
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
