One limitation of the GROUP_CONCAT function is that you can’t directly limit it’s output. Say for example you have an order table and a products table and you wanted to list all the orders for a specific product, you could probably do this:
SELECT p.product_name, GROUP_CONCAT(o.order_id ORDER BY order_id DESC SEPARATOR ', ') as order_ids FROM product p INNER JOIN orders o ON (o.product_id = p.product_id) GROUP BY p.product_id
This would produce this:
+--------------------------------+------------------------+ | product_name | order_ids | +--------------------------------+------------------------+ | some laptop | 100223, 100266, 100298 | +--------------------------------+------------------------+
However, say for example you only want to show the last 2 orders for this product, you can do the following:
SELECT p.product_name, SUBSTRING_INDEX( GROUP_CONCAT( o.order_id ORDER BY order_id DESC SEPARATOR ', ' ),', ',2 ) as order_ids FROM product p INNER JOIN orders o ON (o.product_id = p.product_id) GROUP BY p.product_id
The select statement does select ALL of the matching rows from the tables, however, the SUBSTRING_INDEX function truncates the amount of values by counting the ‘, ‘ character occurrences. Here’s the result:
+--------------------------------+------------------------+ | product_name | order_ids | +--------------------------------+------------------------+ | some laptop | 100223, 100266 | +--------------------------------+------------------------+
Permalink: http://www.websitefactors.co.uk/mysql/2011/07/mysql-limit-the-values-in-the-group_concat-function/
This was very helpful – thanks.
Now I guess the only issue would be to test the performance of the substring_index function on large child record volumes.
Yes, that would be interesting to see.
just solved my problem, thx