On Tuesday, 15 August, 2017 13:27, Jens Alfke <[email protected]> said: >> On Aug 15, 2017, at 12:22 PM, Keith Medcalf <[email protected]> wrote:
>> Well, the documentation is incorrect. The ordering is entirely >>deterministic. The items presented to the aggregate are concatenated >>in the order in which they are presented to the aggregate function, >>and this ordering is determined solely by the traversal order of the >>underlying table from which the data is drawn. >…which is arbitrary, from the POV of a client of SQLite. The >documentation warns against making assumptions about unordered table >traversal, and even offers "pragma reverse_unordered_selects” to >deliberately perturb it, to flush out code that might be relying on >consistent ordering. "To an outside observer any sufficiently advanced technology may appear to be magic" I did not specify "hopefulness or luck". I was specific. The order of the concatenated output is based on the order in which the items to be concatenated are fed into the concatenation function. That is to say, to use precise newfy speak, they are concatenated side after each. If you wish the side after each output to be ordered, then you need to control the order is which the data is presented. In order to be truly "arbitrary" the group_concat aggregate would need to use a (true) randomness source to determine the insertion point of each item in the growing list. Barring the use of a source of randomness, whether the concatenation was performed side-by-each first-is-first to last-is-last; or, side-by-each last-is-first to first-is-last; or even appended by alternate ends even to front, odd to end (or vice versa) the concatenation order is entirely deterministic and is based on the ordering of the data fed into the aggregate and can be fully and completely controlled controlling the input order. --- 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 [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
