SQL - call aggregate function in sort order.

Question

I have a custom aggregate function that concatenates items with a delimiter. How can I call this function to ensure that the result is a sorted list.

Aggregate functions like sum() will have the same result no matter which order the rows are fed to it.

I can't easily sort the items in the aggregate function itself because the sort order depends on a different field.

For example:

product|size_id   | size_string
--------------------------------
prod1  |10        | x-small
prod1  |20        | small
prod1  |30        | medium
prod1  |40        | large
prod1  |50        | x-large

I would like a query to return:

prod1, available in: x-small,small,medium,large,x-large

I.e. in the sizes concatenated in the order of (size_id)

SELECT product,concat_agg(size_string) GROUP BY product;

will return the required data, but not in sorted order.

At the moment I'm using a function to cycle through the sizes in the correct order and adding them to the result. I suspect there might be an SQL way to have a subquery produce the desired result. There must be other aggregate functions that are not commutative.

Answer 1

You can add an order by to the GROUP_CONCAT function (under MySQL:

SELECT product, GROUP_CONCAT(size_string ORDER BY size_id SEPERATOR ',')
GROUP BY product

Here is the official help for GROUP_CONCAT.