Re: [sqlite] Ordering of group_concat values using subselect in 3.8.7

[James Earl]

On Tue, Oct 28, 2014 at 1:50 PM, Clemens Ladisch  wrote:
> 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

That works, thank you!  I always feel lucky when I get to use SQLite.  ;)

James
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Ordering of group_concat values using subselect in 3.8.7

[James Earl]

On Tue, Oct 28, 2014 at 1:43 PM, Simon Slavin <slav...@bigfraud.org> wrote:
>
> On 28 Oct 2014, at 7:33pm, James Earl <ja...@truckhardware.ca> 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.
>
> Sorry, but as the documentation says
>
> <https://www.sqlite.org/lang_aggfunc.html>
>
> "The order of the concatenated elements is arbitrary."
>
> I think the change you're seeing is the result of improved optimisation in 
> 3.8.7.  It might be possible to get the order you want by changing the 
> phrasing of your query from a sub-select to JOIN, but it's already 
> complicated and I can't figure out the right phrasing right now.

Thank you Simon.  It was a nice hack while it worked!  I don't mind
doing things differently.

James
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Ordering of group_concat values using subselect in 3.8.7

[James Earl]

Hi,

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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users