> Did you try something like: > > SELECT id + 1 FROM foo WHERE id>= 100 AND id< 200 and id = MAX (id);
Probably you meant SELECT id + 1 FROM foo WHERE id>= 100 AND id< 200 ORDER BY id DESC LIMIT 1; Otherwise it's incorrect SQL. Pavel On Mon, Feb 22, 2010 at 5:27 PM, Jim Morris <jmor...@bearriver.com> wrote: > Did you try something like: > > SELECT id + 1 FROM foo WHERE id>= 100 AND id< 200 and id = MAX (id); > > > > On 02/22/2010 7:02 AM, ArtemGr wrote: >> Simon Slavin<slav...@...> writes: >> >>> Just out of interest, and I know that theoretically this is not an optimal >>> statement, but have you compared >>> this with the results of putting the '+1' in the brackets ? >>> >> Thanks for the pointer, Simon. >> >> Looking back to my analizys, I see that it was wrong. >> SELECT MAX (id) FROM foo WHERE id>= 100 AND id< 200; >> actually jumps to Close directly after AggStep, >> it would only read a single row to produce the result. >> >> No suck luck for >> SELECT MAX (id) + 1 FROM foo WHERE id>= 100 AND id< 200; >> which goes thru the whole range. >> >> >> As for MAX (id) + 1 versus MAX (id + 1), >> >> SELECT MAX (id) + 1 FROM foo WHERE id>= 100 AND id< 200; >> calculates MAX (id) in a cycle, then it adds 1 outside of the cycle, >> before submitting ResultRow. >> >> SELECT MAX (id + 1) FROM foo WHERE id>= 100 AND id< 200; >> increments id in every iteration of the cycle and passes the result to max. >> >> MAX (id + 1) is clearly less optimizable than MAX (id) + 1. >> Obvioulsy, SQLite already have a special case optimization for MAX (id), >> but optimization breaks with MAX (id) + 1, making it impractical for use in >> INSERT SELECT. >> >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
