Hello,
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.
The correct result can be obtained with the query:
sqlite> SELECT MIN(a), b FROM T GROUP BY b ORDER BY a;
1|Dog
2|Cat
3|Mouse
Regards,
-LN
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
