On Mon, Apr 25, 2011 at 8:35 AM, Petite Abeille <petite.abei...@gmail.com> wrote: > On Apr 25, 2011, at 7:57 AM, Nico Williams wrote: >> For group_concat(), however, this undefined order is >> obnoxious. > > Seconded. As it stands, group_concat nondeterministic nature renders it > useless for most practical purposes.
I wouldn't say "useless". For unordered relations it works fine. For ordered relations I've taken to applying group_concat() to the concatenation of the ordering expression and the value expression, like so: SELECT ..., group_concat(list_order || ':' || value, ',') ...; > For what's worth, here is the syntax adopted by Oracle for its LISTAGG > function [1]: > > LISTAGG( expression, delimiter ) WITHIN GROUP ( ORDER BY clause ) If I understand correctly, that's exactly the syntax that I'd like: WITHIN GROUP (<order-by-exps>). It allows the query optimizer and engine to implement a single intra-group order of application of all aggregate functions. This is much more reasonable, IMO, than the MySQL syntax. > As group_concat already supports 'distinct', perhaps tagging 'order by' at > the end of the expression would not be that bad: > > GROUP_CONCAT( DISTINCT expression ORDER BY clause, delimiter ) Yes, but that's not indicative of the VM having sufficient functionality to implement intra-group order. However, looking at the VDBE opcodes superficially, and at the differences between the VDBE code generated when using or not using DISTINCT this way, it does look like the VM does have all that is needed to implement intra-group ordering. > EIther way, irrespectively of syntax, group_concat would greatly benefit from > a deterministic ordering :)) Indeed! Nico -- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
