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
