This issue is debated from time to time on the list:
http://www.mail-archive.com/[email protected]/msg17769.html
The only other database that I'm aware of that supports selecting
non-aggregates that are not listed in GROUP BY is MySQL:
-- valid in sqlite and mysql, invalid in postgres
select b from t group by a;
But your particular example is not valid in MySQL:
mysql> SELECT MAX(a), b FROM T;
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no
GROUP columns is
illegal if there is no GROUP BY clause
--- Lauri Nurmi <[EMAIL PROTECTED]> wrote:
> SQLite seems to be accepting SELECT queries that use aggregate functions
> without a GROUP BY. This is a little dangerous, because queries that
> should not work at all are returning sensible-looking results.
>
> Example:
>
> Let's have a simple table T with the following structure and content:
>
> CREATE TABLE T(a INTEGER, b TEXT);
> INSERT INTO "T" VALUES(1,'Dog');
> INSERT INTO "T" VALUES(2,'Cat');
> INSERT INTO "T" VALUES(3,'Mouse');
> INSERT INTO "T" VALUES(6,'Cat');
> INSERT INTO "T" VALUES(7,'Mouse');
>
> Now, let's say we want the maximum value of "a" and the animal name
> related to it. Easy:
>
> sqlite> SELECT MAX(a), b FROM T;
> 7|Mouse
>
> The result was as expected, and everyone is happy?
>
> Let's find the minimum of "a" and the related animal name:
>
> sqlite> SELECT MIN(a), b FROM T;
> 1|Mouse
>
> Wait -- this is not what we expected. But in a database with hundreds
> or thousands of lines we might not have noticed the result is wrong.
> Also the result of the previous MAX(a) query was "correct" only by
> coincidence.
____________________________________________________________________________________
Looking for last minute shopping deals?
Find them fast with Yahoo! Search.
http://tools.search.yahoo.com/newsearch/category.php?category=shopping
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
