Re: [sqlite] SQLite 3.1.0 Group By problem

[Derrell . Lipman]

"[EMAIL PROTECTED]" <[EMAIL PROTECTED]> writes:

> Hi All,
>
> First, Thank you for excellent piece of software!
> I am finding following problem would like to share:
> ---------------
> SELECT a FROM [table] GROUP BY a;
> Error Message in Sqlite 3.1.0 alpha: "SQL error: GROUP BY may only be used on
> aggregate queries"

Although the query you are issuing used to be allowed, it probably
(technically) shouldn't have been since GROUP BY is primarily intended for
applying aggregate functions.  The query that you likely intend by this is,
and that should give the same result as you used to get, is:

  SELECT DISTINCT a FROM [table];

Here's an example, using an older version that allowed the GROUP BY, showing
the same result set with this DISTINCT query:

% sqlite :memory:
SQLite version 2.8.15
Enter ".help" for instructions
sqlite> create table [table] (a integer, b integer);
sqlite> insert into [table] values (23, 42);
sqlite> insert into [table] values (2, 3);
sqlite> insert into [table] values (2, 5);
sqlite> insert into [table] values (23, 56);
sqlite> insert into [table] values (42, 23);
sqlite> select a from [table] group by a order by a;
    a = 2

    a = 23

    a = 42
sqlite> select distinct a from [table] order by a;
    a = 2

    a = 23

    a = 42
sqlite> 

Derrell