On 11/23/2011 11:17 AM, Wiktor Adamski wrote:
SQLite version 3.7.9 2011-11-01 00:52:41
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table t(a);
sqlite> select avg(a) from t order by avg(a); -- order by aggregate
possible
sqlite> select 1 from t order by a; -- order by column not in result
possible
sqlite> select 1 from t group by 1 order by avg(a); -- order by
aggregate not in result possible
sqlite> select 1 from t order by avg(a); -- should be possible
Error: misuse of aggregate: avg()
sqlite>
Using an aggregate function anywhere in the list of selected fields,
and/or specifying a GROUP BY clause, turns a regular SELECT statement
into an aggregate one. Apparently, using such a function in ORDER BY
clause alone doesn't make the statement aggregate (whether it should is
perhaps debatable), so the condition in ORDER BY applies to each row,
not to each group. But then, it is an error to use an aggregate function
there (for the same reason that using one in a WHERE clause is an error).
The other two queries in your example that have ORDER BY avg(a) are made
in fact aggregate statements, either by using avg() in the SELECT
clause, or by having GROUP BY.
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
