>We can observe GROUP BY works ASCending only as of now. Why it can't work >DESCending to avoid ordering, that's a different question. >From https://www.sqlite.org/lang_select.html we can observe that >GROUP BY takes an expr on the RHS, while ORDER BY takes an expr >followed by optional COLLATE and ASC/DESC terms.
The GROUP BY clause does not imply ordering. The fact that the output is ordered is an implementation detail -- the grouping could be implemented by a hash table, in which case the output would be ordered by hash value, for instance. All that the expression in a GROUP BY does is determine the groupings, and therefore the expression is limited to a comparison compatible expression. For example, you can GROUP BY x COLLATE NOCASE which implies that the groups are formed using case insensitive comparisons of x. The ORDER BY clause determines the output ordering. You will note that if you do the following: create table x(x,y); create index ix on x(x desc, y); select x, someaggregate(y) from x group by x order by x desc; then ix will be used as a covering index (which is good) however the group by x is treated as an ordering expression, not as simply a grouping expression. In fact the code that implements the group by does indeed (perhaps erroneously) treat the group by expression as implying order, since it will traverse the covering index in reverse order so that the output from GROUP BY is in ascending order, and add an extra sort to do the ORDER BY. That means the GROUP BY code generator is already capable of traversing the selected index in reverse order when necessary. It appears that the optimizer however does not recognize that the "desc" attribute from the order by can be "pushed down" into the GROUP BY (which really is ordering as an implementation detail) thus eliminating the ORDER BY processing entirely. Note that you cannot specify that the GROUP BY is ordering -- it will not accept the ASC or DESC keywords (which is correct), and this should not be changed, however, treating it as being ordering when it is not might perhaps be a defect ... _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
