MySQL GROUP_CONCAT with ORDER BY returning results in the wrong order

SELECT * with GROUP BY is not a good practice, that raises error in versions of MySQL where ONLY_FULL_GROUP_BY is enabled (which is default since version 5.7).

Same goes for LIMIT without ORDER BY: which result will appear in the resultset cannot be told consistantly.

You maybe want:

SELECT ean, GROUP_CONCAT(id ORDER BY price ASC) ids
FROM mytable 
GROUP BY ean
ORDER BY ean
LIMIT 500

Or:

SELECT ean, GROUP_CONCAT(id) ids
FROM mytable 
GROUP BY ean
ORDER BY MIN(price)
LIMIT 500

Try this option:

SELECT ean, GROUP_CONCAT(id) AS ids
FROM yourTable
GROUP BY ean
ORDER BY MIN(price)
LIMIT 500

This orders each ean group by the smallest price among all records.

One important characteristic of GROUP_CONCAT function is, that the ORDER BY clause allows you to sort the values in ascending or descending order before concatenating.

By default, it sorts the values in ascending order. If you want to sort the values in the descending order, you need to specify explicitly the DESC option.

Also GROUP_CONCAT function ignores NULL values. so if it happens that there are items without a price in your case, it will return NULL if there was no matching row found or all arguments are NULL values.

Another important thing: the default characters length GROUP_CONCAT is 1024.

If 1024 characters length is fine for you requirement then there is nothing to worry. But if not, you have to modify the GROUP_CONCAT character length as per your requirement as below:

  SET SESSION group_concat_max_len = 1000000;