On Thu, 19 Dec 2013 15:14:24 +0100 Dominique Devienne <ddevie...@gmail.com> wrote:
> The selling point of SQL is to declaratively tell the engine what you > need, and let it choose the optimal implementation. So saying that > ORDER BY doesn't know about LIMIT as a matter of fact seems > completely wrong to me. It may help to remember that ORDER BY, properly understood, is a post-processing clause. It takes a table object as input and writes a cursor as output. The use of ORDER BY in other ways has caused quite a bit of teeth-gnashing on this list. Most queries that use LIMIT could be expressed without it. I was glad to see SQLite reward your use of max(). :-) Looking at your queries, > sqlite> select max(id) from t100m limit 1; LIMIT is redundant. > sqlite> select 1 where exists (select id from t100m order by id desc); > sqlite> select 1 where exists (select id from t100m where id < > 500*1000 order by id desc); > sqlite> select 1 where exists (select id from t100m where id = 13 > order by id desc); ORDER BY in subquery unnecessary, probably deleterious. I suggest "SELECT 1" in an EXISTS clause for clarity of intent. You might want to try select * from t100m as t where exists ( select 1 from t100m having max(id) = t.id ); which I think is more along the lines of what you want. I doubt it will be any faster, though. Without an index SQLite must scan the table. HTH. --jkl _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users