Here's the query with standard group_concat and cast: SELECT CAST(GROUP_CONCAT(`expScores` ORDER BY `i` SEPARATOR '') AS BINARY) AS `expScores` FROM `probes` LEFT JOIN `joins` ON `probes`.`id` = `pid` LEFT JOIN `scores` ON `sid` = `scores`.`id` WHERE (`probes`.`eid` = (SELECT id FROM `experiments` WHERE (`experiments`.`file` = ?))) GROUP BY `joins`.`pid`
The custom aggregate is a binary concat, group_bconcat which avoids the implicit cast in group_concat by concatenating primitive arrays. Works with BINARY and ARRAY types. The alternative is to do the concat in the application code, which perhaps I should do. It looks like the ORDER BY capability is hard-coded into the parser for group_concat, rather than being a generic option for aggregate functions. On Friday, July 26, 2013 12:34:13 AM UTC-7, Noel Grandin wrote: > > Do you have a more complete example of what exactly you are trying to do > with your custom aggregate? > > On 2013-07-26 00:34, Brian Craft wrote: > > I'm unable to use "order by" with a custom aggregate function. E.g > > with group_concat, you can do > > > > group_concat(`foo` order by `bar`) > > > > however this throws a syntax error with my aggregate function. > > > > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/groups/opt_out.
