On Tue, 30 Jan 2018 12:16:32 +0100, Stephan Buchert <stephanb...@gmail.com> wrote:
> Thanks for the replies. > > Allowing non-aggregate columns in aggregate queries is very useful, as > shown with the min/max functions. It is forbidden in most SQL dialects, only supported by SQLite as a dirty shortcut. > Probably with this feature comes that SQLite even allows all non-aggregate > columns in SELECTs with GROUP BY. Perhaps the documentation should warn > more clearly, that in this case only one arbitrary row in each group is > returned, not all the rows that the WHERE filter lets through. IMHO it is pretty clear in https://sqlite.org/lang_select.html "3. Generation of the set of result rows" and its "Side note: Bare columns". Also, just above "1. Determination of input data" it states: There are two types of simple SELECT statement - aggregate and non-aggregate queries. A simple SELECT statement is an aggregate query if it contains either a GROUP BY clause or one or more aggregate functions in the result-set. Otherwise, if a simple SELECT contains no aggregate functions or a GROUP BY clause, it is a non-aggregate query. > More useful would perhaps be, to return in this case (only non-aggregate > columns but a GROUP BY) all rows, just grouped together is indicated by > the GROUP BY. This would have a similar effect as an ORDER BY, but they are > somewhat different if I look at the syntax diagrams. I have no idea how > feasible it would be to get SQLite doing this. ORDER BY does the job just fine, there's no need to overload GROUP BY with that functionality. -- Regards, Kees Nuyt _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
