group by - Is there ANY_VALUE capability for mysql 5.6?

Question

currently im working with mysql 5.7 in development, and 5.6 in production. Each time i run a query with a group by in development i get some error like "Error Code: 1055. Expression #1 of SELECT list is not in GROUP BY "

Here is the query.

SELECT c.id, c.name, i.* 
 FROM countries c, images i 
WHERE i.country_id = c.id
GROUP BY c.id; Fixed for 5.7; 

SELECT c.id, c.name,
       ANY_VALUE(i.url) url, 
       ANY_VALUE(i.lat) lat, 
       ANY_VALUE(i.lng) lng 
  FROM countries c, images i
 WHERE i.country_id = c.id
 GROUP BY c.id;

For solving that I use the mysql function from 5.7 ANY_VALUE, but the main issue is that its not available in mysql 5.6

So if I fix the sql statement for development i will get an error in production.

Do you know any solution or polifill for the ANY_VALUE function in mysql 5.6?

Answer 1

Instead of ANY_VALUE, you could use the MIN or MAX aggregate functions.

Alternatively, you might consider not setting the ONLY_FULL_GROUP_BY SQL mode, which is set by default for MySql 5.7, and is responsible for the difference you experience with MySql 5.6. Then you can delay the update of your queries until you have migrated all your environments to MySql 5.7.

Which of the two is the better option, is debatable, but in the long term it will be better to adapt your queries so they adhere to the ONLY_FULL_GROUP_BY rule. Using MIN or MAX can certainly be of use in doing that.