On Thu, 19 Dec 2013 15:14:24 +0100
Dominique Devienne <[email protected]> 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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
