Re: [sqlite] max() with LIMIT
On Wed, 31 Aug 2011 08:12:10 -0400, "Igor Tandetnik" wrote: >Ivan Shmakov wrote: >>> Tobias Vesterlund writes: >>> Is it possible to get the highest value in a "limited column" when >>> using LIMIT? >> >> Sure. >> >> SELECT max (id) FROM (SELECT id FROM t WHERE id > 0 LIMIT 10); > > This only works by accident. There's no requirement > that the subselect return rows in any particular order. > It can, in principle, choose any ten rows. You're right. I should have taken that into consideration in my posting in this thread. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] max() with LIMIT
> Igor Tandetnik writes: > Ivan Shmakov wrote: > Tobias Vesterlund writes: >>> Is it possible to get the highest value in a "limited column" when >>> using LIMIT? >> Sure. >> SELECT max (id) FROM (SELECT id FROM t WHERE id > 0 LIMIT 10); > This only works by accident. There's no requirement that the > subselect return rows in any particular order. It can, in principle, > choose any ten rows. There wasn't the requirement that the query should be unambiguous in its interpretation in the OP, either. However, yes, almost anytime LIMIT is used, it has to be complemented with ORDER BY for a definite result. -- FSF associate member #7257 Coming soon: Software Freedom Day http://mail.sf-day.org/lists/listinfo/ planning-ru (ru), sfd-discuss (en) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] max() with LIMIT
On 31 Aug 2011, at 8:38am, Tobias Vesterlund wrote: > Is it possible to get the highest value in a "limited column" when using > LIMIT? Sort by that value. For instance, SELECT id FROM t ORDER BY id DESC LIMIT 1 will give you the biggest value of 'id' that can be found. It's not needed in this case, but if you sort using a different field, make sure there's an index which allows quick sorting on that field. It doesn't matter whether the index specifies that field as ASC or DESC. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] max() with LIMIT
Tobias Vesterlund wrote: > 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. > > Is it possible to get the highest value in a "limited column" when using > LIMIT? You seem to want the tenth smallest ID. Try this: select id from t where id > 0 order by id limit 1 offset 9; -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] max() with LIMIT
Ivan Shmakov wrote: >> Tobias Vesterlund writes: >> Is it possible to get the highest value in a "limited column" when >> using LIMIT? > > Sure. > > SELECT max (id) FROM (SELECT id FROM t WHERE id > 0 LIMIT 10); This only works by accident. There's no requirement that the subselect return rows in any particular order. It can, in principle, choose any ten rows. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] max() with LIMIT
On Wed, 31 Aug 2011 09:38:46 +0200, Tobias Vesterlund 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
Re: [sqlite] max() with LIMIT
Right you are, thank you! Regards, Tobias -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Ivan Shmakov Sent: den 31 augusti 2011 10:13 To: sqlite-users@sqlite.org Subject: Re: [sqlite] max() with LIMIT >>>>> Tobias Vesterlund writes: [...] > 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. LIMIT applies /after/ the aggregation. Consider, e. g., using GROUP BY, and then selecting no more than 10 groups' maximums. > Is it possible to get the highest value in a "limited column" when > using > LIMIT? Sure. SELECT max (id) FROM (SELECT id FROM t WHERE id > 0 LIMIT 10); -- FSF associate member #7257 Coming soon: Software Freedom Day http://mail.sf-day.org/lists/listinfo/ planning-ru (ru), sfd-discuss (en) ___ 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
Re: [sqlite] max() with LIMIT
> Tobias Vesterlund writes: […] > 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. LIMIT applies /after/ the aggregation. Consider, e. g., using GROUP BY, and then selecting no more than 10 groups' maximums. > Is it possible to get the highest value in a "limited column" when > using LIMIT? Sure. SELECT max (id) FROM (SELECT id FROM t WHERE id > 0 LIMIT 10); -- FSF associate member #7257 Coming soon: Software Freedom Day http://mail.sf-day.org/lists/listinfo/ planning-ru (ru), sfd-discuss (en) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] max() with LIMIT
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. Is it possible to get the highest value in a "limited column" when using LIMIT? Regards, Tobias ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
