Yes, I have added the min and things work fine. select 'Start2';select min(c1),c2 from T0 group by c2 limit 1;
Absent the min statement, and no other constraints, SQLite should take the first item and call it quits. select 'Start1';select * from T0 group by c2 limit 1; The second example without the min picks the last of the c2, not the first. On 5/7/10, Matt Young <youngsan...@gmail.com> wrote: > On 5/7/10, Pavel Ivanov <paiva...@gmail.com> wrote: >> Sorry, I can hardly understand what you are trying to say but want to >> point out one error: >> >>> sqlite> select 'Start';select * from T0 group by c2; >> >> This is incorrect SQL. SQLite silently accepts it but its behavior is >> undefined in such situation. Any other database wouldn't execute such >> SQL. If you want to make it correct you should write something like >> this: >> >> select max(c1), c2 from T0 group by c2; >> or >> select min(c1), c2 from T0 group by c2; >> or >> select sum(c1), c2 from T0 group by c2; >> >> Other aggregate variants are possible too. In general if you use >> "group by" clause then only columns from "group by" clause can be >> mentioned in select list, all other columns MUST be inside some sort >> of aggregate function (http://www.sqlite.org/lang_aggfunc.html). >> >> >> Pavel >> >> On Fri, May 7, 2010 at 2:02 PM, Matt Young <youngsan...@gmail.com> wrote: >>> In the following code I try select by group on one column. I am >>> assuming the query optimizer will figure to stop gathering column 1s >>> right away because there are no other selection constraints. Yet in >>> the example, sql still looks through the entire table for additional >>> column ones as one can see by looking as the column key it produces, >>> the column key at the end of my group in column1. >>> >>> >>> C:\R\SQLite>sqlite3 >>> SQLite version 3.6.23.1 >>> Enter ".help" for instructions >>> Enter SQL statements terminated with a ";" >>> sqlite> drop table T0; >>> Error: no such table: T0 >>> sqlite> create table T0( >>> ...> c1 INTEGER PRIMARY KEY autoincrement, >>> ...> c2 text); >>> sqlite> >>> sqlite> >>> sqlite> insert into T0 (c2) values ('a'); >>> sqlite> insert into T0 (c2) values ('a'); >>> sqlite> insert into T0 (c2) values ('a'); >>> sqlite> insert into T0 (c2) values ('b'); >>> sqlite> insert into T0 (c2) values ('b'); >>> sqlite> insert into T0 (c2) values ('c'); >>> sqlite> insert into T0 (c2) values ('a'); >>> sqlite> insert into T0 (c2) values ('d'); >>> sqlite> >>> sqlite> select 'Start';select * from T0; >>> Start >>> 1|a >>> 2|a >>> 3|a >>> 4|b >>> 5|b >>> 6|c >>> 7|a >>> 8|d >>> sqlite> select 'Start';select * from T0 group by c2; >>> Start >>> 7|a >>> 5|b >>> 6|c >>> 8|d >>> sqlite> >>> >>> >>> In other words, is group by searching rows unnecessarily. I did not >>> ask for the last one in the group? >>> _______________________________________________ >>> 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
