James Earl wrote:
> After upgrading from SQLite 3.8.6 to 3.8.7, I am no longer able to
> order group_concat values by using a subselect. For example the
> following query with 3.8.6 will give me an ordered string of
> items.image values based on items.position (which contains integers):
>
> SELECT products.name, GROUP_CONCAT(items.image) AS item_images
> FROM products
> INNER JOIN (
> SELECT * FROM items ORDER BY position
> ) AS items ON (items.product_id = products.id)
> GROUP BY products.id
> ORDER BY products.name
Joining typically involves reordering rows. You were lucky in 3.8.6.
You have to order the result of the join before grouping:
SELECT product_name,
GROUP_CONCAT(item_image) AS item_images
FROM (SELECT products.id AS product_id,
products.name AS product_name,
items.image AS item_image
FROM products
JOIN items ON items.product_id = products.id
ORDER BY products.id,
items.position)
GROUP BY product_id
ORDER BY product_name
Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
