On Wed, 31 Aug 2011 09:38:46 +0200, Tobias Vesterlund <tobias.vesterl...@ericsson.com> wrote:
> Hi, > > I'm want to get the max value out of a certain column in a table. > > Table t has a column named id, which ranges from 0 to 99. > > If I do SELECT max(id) FROM t; > it will return 99. > If I do SELECT id FROM t WHERE id > 0 LIMIT 10; > it will return 1,2,3,4,5,6,7,8,9,10 > But If I do SELECT max(id) FROM t WHERE id > 0 LIMIT 10; > it will return 99. > > My logic, which may be flawed in this case, > tells me the third SELECT should return 10 and not 99. http://www.sqlite.org/lang_select.html tells: The LIMIT clause is used to place an upper bound on the number of rows returned by a SELECT statement. [...] Otherwise, the SELECT returns the first N rows of its result set only, where N is the value that the LIMIT expression evaluates to. Or, if the SELECT statement would return less than N rows without a LIMIT clause, then the entire result set is returned. Your statement SELECT max(id) FROM t WHERE id > 0 always returns exactly one row, so LIMIT has no effect here. > Is it possible to get the highest value in a "limited column" when using > LIMIT? It is unclear to me what you mean with limited column. Perhaps this statement is what you are looking for? SELECT max(id) FROM ( SELECT id FROM t WHERE id > 0 LIMIT 10); -- ( Kees Nuyt ) c[_] _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
