On 19 Feb 2013, at 9:19am, "Gabriel Corneanu" <[email protected]> wrote:
> As a summary, it seems that having multiple "order by" disturbs the query > builder; of course, I expected the "optimizer" to recognize that i was the > same order and avoid extra sorting. > Am I doing a mistake?? I think you have figured it out correctly. SQLite prepares the data you asked for in two stages. First it executes the SELECT which you defined to make the VIEW. Then it executes a SELECT on the results. It does not merge the two SELECTs into one command, so it never notices that the result of the first SELECT already has the rows in the right order. I think you laid out the three possible approaches to ORDER BY nicely and can pick whichever one suits you best: either depend on the VIEW always returning rows in the right order or don't. However, in SELECT from a TABLE (rather than from a VIEW) you can never depend on the order of the returned rows. So perhaps it's better not to have your VIEW do sorting. That way your VIEW has the same behaviour as SQL users would expect from a TABLE. Simon. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
