On Tue, 19 Feb 2013 10:19:26 +0100
"Gabriel Corneanu" <gabrielcorne...@gmail.com> wrote:
> I included the "order by" in view because it's meant for some
> end-users and I wanted to avoid mistakes.
...
> Am I doing a mistake??
Well, yes, by including ORDER BY in the view definition. Most DBMSs
don't allow that, and the SQL standard doesn't allow it. So don't do
it! :-)
ORDER BY is best understood as *not* part of the SELECT statement.
Rather, it's a post-processor. Consider that there can be many SELECTs
in a query, but only one ORDER BY.
According to the SQL standard, SELECT produces a "table expression" that
can be used wherever a table can be used. ORDER BY *reads* a table
expression; what it returns is technically a "cursor".
It's tempting to think, OK, but the view's ORDER BY would be processed
first and the final ORDER BY would be processed last, so the order is
predictable. In fact, though, there is no first and last. The SQL
statement is a declaration, not an imperative. It describes which rows
and columns to retrieve. It specifies only an outcome, not an
algorithm or an order of operation.
In effect, your query specified
ORDER BY id
AND
ORDER BY data
which you would never do, and SQLite can't, either. ;-)
HTH.
--jkl
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
