MySQL GROUP_CONCAT with ORDER BY returning results in the wrong order

133
November 18, 2019, at 9:20 PM

I have a table that stores product information which includes the fields: id(unique), ean, price.

I am trying to group all results with the same ean number, and then order by price. I am using GROUP_CONCAT because some ean records are NULL. I am using the following query:

SELECT GROUP_CONCAT(id), * FROM table GROUP BY ean ORDER BY price ASC LIMIT 500

The results returned are not ordered by price ascending. I have tried adding ORDER BY into the GROUP_CONCAT, however this is returning a MySQL error:

SELECT GROUP_CONCAT(id ORDER BY price ASC), * FROM table GROUP BY ean LIMIT 500

Syntax error or access violation

Answer 1

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
Answer 2

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.

Answer 3

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;
Rent Charter Buses Company
READ ALSO
PHP Mysql Prepared statement different result [duplicate]

PHP Mysql Prepared statement different result [duplicate]

This question already has an answer here:

94
perl - insert value if column word is missing

perl - insert value if column word is missing

in perl, i want to insert missing line if

100
Routine import data from Excel to MySQL Database

Routine import data from Excel to MySQL Database

I know that it's possible to do a manual import of data from an Excel file to a MySQL database

94