> Absent the min statement, and no other constraints, SQLite should take > the first item and call it quits.
No, it shouldn't. As I said without min() it's invalid SQL and SQLite has the right to do whatever it wants to. But of course I'd better see it returning error and not executing such SQL at all. Pavel On Fri, May 7, 2010 at 3:38 PM, Matt Young <youngsan...@gmail.com> wrote: > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
