> -----Original Message----- > From: [email protected] > [mailto:[email protected]] On Behalf > Of James K. Lowden > Sent: Tuesday, February 19, 2013 12:07 PM > To: [email protected] > Subject: Re: [sqlite] query optimization with "order by" in a view > > On Tue, 19 Feb 2013 10:19:26 +0100 > "Gabriel Corneanu" <[email protected]> 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
Very nice explanation, James ! Thank you. -- kjh( I had to look twice to make sure I was in my SQLite Mailbox and not in FreeTDS :) _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
