1

If I have a table like this:

  bagid  fruit  color
  -----  -----  -----
  1      apple  red
  1      banana yellow

and I select bagid,GROUP_CONCAT(fruit),GROUP_CONCAT(color) grouped by bagid, can I count on the results looking like this:

1 apple,banana red,yellow

or will they sometimes look like this:

1 apple,banana yellow,red

In other words, will the two concatenations always be ordered in the same way? That seems to be the case, but I'd like to be sure. For what it's worth, I'm using Mariadb 10.3.

1
  • Consider, as a single column, GROUP_CONCAT(CONCAT(fruit, ' - ', color))
    – Rick James
    Sep 30, 2022 at 17:06

1 Answer 1

1

You are not guaranted that they will be in the same order, eventhogh it likely will happen. Not even adding an ORDER BY at the end of your query will guarantee it, since that is logically evaluated after the aggregate functions.

You can however get the same order if you add the same order by to both GROUP_CONCAT:

SELECT bagid
     , GROUP_CONCAT(fruit ORDER BY fruit)
     , GROUP_CONCAT(color ORDER BY fruit) 
FROM t
GROUP BY bagid
2
  • Ahh! Of course. Thanks for pointing that out. Sep 30, 2022 at 16:07
  • Why is that not guaranteed (assuming the ORDER BY is deterministic)? Oct 2, 2022 at 22:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.