Simon Slavin wrote: > On 19 Aug 2017, at 10:48pm, Cecil Westerhof wrote: >> I was also told that you never should put a sort on a view. Is that true, >> or a bit to strong? > > Generally, you put the ORDER BY on the SELECT you’re using the consult > the VIEW. Technically speaking a VIEW is just a set of records and the > ORDER BY should be a last-minute thing just before presentation of the > results.
The SQL standard and <http://www.sqlite.org/lang_select.html#orderby> say: | If a SELECT statement that returns more than one row does not have an | ORDER BY clause, the order in which the rows are returned is undefined. ORDER BY clauses in any subqueries/views/CTEs are not guaranteed to keep the order in the outermost query. It's possible for the database to optimize the query by using some index or cache, and that can result in a different order. > But it can be useful for someone submitting commands manually to create > a VIEW with ORDER BY. For instance, using the command-line shell to > investigate weird results, or for those cases where you’re using SQLite > like a spreadsheet to arrive at a one-off result. So I think an outright > ban is a little strong. In practice, when the outer query is implemented by simply scanning over the inner query's rows, the order will not change. But there is no guarantee that the implementation will always stay the same. Regards, Clemens _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
