Hi Keith, thanks for the explanation.
PS: I used a CTE because official examples (e.g. Mandelbrot) also used CTEs in combination with group_concat. Although the incorporation of group_concat was not the primary reason to use CTEs. PPS: Is it possible to rephrase the documentation for group_concat in the sense that it does not mention that the order is (always) arbitrary? Regards, Hardy > Am 2020-03-02 um 04:46 schrieb Keith Medcalf <kmedc...@dessus.com > <mailto:kmedc...@dessus.com>>: > > > On Sunday, 1 March, 2020 14:58, mailing lists <mailingli...@skywind.eu > <mailto:mailingli...@skywind.eu>> wrote: > >> Assume I create the following table: > >> CREATE TABLE Test (ID INTEGER PRIMARY KEY, Value TEXT); >> INSERT INTO Test (Value) VALUES('Alpha'); >> INSERT INTO Test (Value) VALUES('Beta'); >> INSERT INTO Test (Value) VALUES('Beta'); >> INSERT INTO Test (Value) VALUES('Alpha'); > >> According to the documentation of group_concat the order is undefined, >> indeed: > >> SELECT group_concat(DISTINCT Value) FROM Test ORDER BY Value ASC; >> SELECT group_concat(DISTINCT Value) FROM Test ORDER BY Value DESC; > >> Both queries result in Alpha,Beta. > >> Changing the queries to > >> WITH Result(x) AS (SELECT DISTINCT Value FROM Test ORDER BY Value ASC) >> SELECT group_concat(x) FROM Result; >> WITH Result(x) AS (SELECT DISTINCT Value FROM Test ORDER BY Value DESC) >> SELECT group_concat(x) FROM Result; > >> leads to the results Alpha,Beta, respectively Beta,Alpha. > >> Is this a coincidence or is this guaranteed to work? > >> Are there any other solutions / possibilities? > > group_concat builds a "group" by concatenating the values sent to it in the > order they are sent. If you do not know this order then for all intents and > purposes the order is "undefined" because it is defined as the order in which > the query planner decides to visit the rows forming the group. SQLite3 > believes that all aggregate functions are commutative and that the order in > which rows are fed into them is immaterial to the result and there > (presently) is no way to specify that this is not the case. > > So in the rare case where the aggregate is not commutative and you depend on > the presentation order, then you must specify it. The only built-in > aggregate that is not commutative is the group_concat function. If you were > to write another non-commutative aggregate function, lets say SHA1(...), that > computed the SHA1 hash of the values fed into it, you would also have to > control the presentation order or the result would be "undefined". > > select group_concat(value) from (select distinct value from test order by > value desc); will do that. (rephrasing as a CTE makes no difference) > > This works because the query as phrased cannot be flattened since the outer > query contains an aggregate and the inner query contains an order by. > > Moving the distinct does not alter the fact that the query cannot be > flattened. > > select group_concat(distinct value) from (select value from test order by > value desc); > > Whether the query planner will always not flatten a query where the outer > query contains an aggregate and the inner query contains an order by is > something on which I cannot comment other than to say that is does not > flatten such a query up to now. > > -- > The fact that there's a Highway to Hell but only a Stairway to Heaven says a > lot about anticipated traffic volume. > > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > <mailto:sqlite-users@mailinglists.sqlite.org> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > <http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users> _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
