On Aug 15, 2017, at 10:39 AM, Bob Friesenhahn <bfrie...@simple.dallas.tx.us> wrote:
> On Tue, 15 Aug 2017, Dan Kennedy wrote: > >> On 08/15/2017 10:12 PM, Bob Friesenhahn wrote: >>> select group_concat(name, ' ') AS 'names' from moca_config where enable == >>> 1 order by name; >> >> Maybe this: >> >> select group_concat(name, ' ') AS 'names' from ( >> SELECT name FROM moca_config where enable == 1 order by name >> ); > > That does return the expected order. Was my expectation unreasonable? Unreasonable? Not really; it is a simple mistake, but it is a mistake. You need to remember that the different clauses of an SQL statement are processed in a specific order. Relevant to this case, ORDER BY is applied *after* GROUP BY, or any other aggregation. Because you have an aggregate function in the SELECT clause, but no explicit GROUP BY, you have an implied GROUP BY across the whole output of the FROM clause. So the aggregation happens and *then* the ORDER BY is applied… except the final output of this statement, as defined by the SELECT clause, has no column named “name” ...so no ordering is enforced. This is also why the sub-select works. It generates a full output, with ordering, and then passes it to the outer statement to do the aggregation. -j > > Bob > -- > Bob Friesenhahn > bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ > GraphicsMagick Maintainer, http://www.GraphicsMagick.org/ > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
