Hi,
The following sql was in a recent post which was complaining about performance and it looks like a solution has been provided for that. However, looking at the original SQL I would have expected an error message to be generated for it because there is no "GROUP BY" clause. SELECT ddate, sum(hits) from a_idadvertiser_site_hostname_bench where ddate < '2013-08-01'; I then looked at the syntax documentation and found the following section, which I think explains the lack of error message and the reason for this result (and my results on testing something similar). If the SELECT statement is an aggregate query without a GROUP BY clause, then each aggregate expression in the result-set is evaluated once across the entire dataset. Each non-aggregate expression in the result-set is evaluated once for an arbitrarily selected row of the dataset. The same arbitrarily selected row is used for each non-aggregate expression. So I think that what this is saying is that when you execute an aggregate query without a GROUP BY, the chosen non-aggregate values are random (i.e. arbitrary). Is the above syntax standard ANSI SQL? Certainly on a Teradata system the above syntax would fail. Cheers, Dave Ward Analytics Ltd - information in motion Tel: +44 (0) 118 9740191 Fax: +44 (0) 118 9740192 www: <http://www.ward-analytics.com> http://www.ward-analytics.com Registered office address: The Oriel, Sydenham Road, Guildford, Surrey, United Kingdom, GU1 3SR Registered company number: 3917021 Registered in England and Wales. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
