On Wednesday, 24 May, 2017 06:07, Denis Burke <burke...@gmail.com> wrote: > These all produce a single row of output (and it happens to be the last > row > inserted [a1,b5]): > select c1,c2 from t1 group by '1'; > select c1,c2 from t1 group by '2'; > select c1,c2 from t1 group by '3'; > select c1,c2 from t1 group by 'c1'; > In the case of string literals, I would have expected to see all rows > returned and in these cases the group by become effectively a no-op. But > I cannot find documentation to justify my expectation, or to validate the > actual output.
In many SQL products this would yield an error of the stating that you can only select either (1) results appearing in the group by clause or (2) the result of an aggregate function applied to the group. That is to say, you can ONLY use group by's of the following form: SELECT <grouping column, ...>, <aggregatefunction(column), ...> FROM t GROUP BY <grouping columns> In the case of grouping by a constant, you have created a group that contains all rows. SQLite, rather than tossing an error, permits you to request columns that do not conform with (1) and (2) above. If you do so, then the returned values for those columns come from "one of the rows" comprising the group. Which particular row, is not defined (to average mortals that is -- in actual fact the values are taken from the last row "visited" that yielded the group aggregate result -- this is called "not defined" because it depends on the vagaries of query optimization and the available indexes -- for a given set of data and known indexes it is 100% determinate -- but that means that you are relying on an implementation detail.) In your case it just happens to be the last row inserted because (1) a table scan is used to retrieve the data for the underlying table (ie, in order by rowid) and (b) the last row inserted happens to have the highest rowid. This means that you can do things like: SELECT a, b, max(c) FROM t GROUP BY a; And you will be returned the groups of values of a, the max value of c in that group, and the value of b from (one of the rows) containing that maximum value. The actual row (assuming multiple rows have the same max(c)) is undefined (but is in fact determined by the visitation order of the underlying table containing c). _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
