How to Aggregate Non-Grouped Columns in MySQL 5.6?

Aggregating without Groups: Solutions for MySQL 5.6

In MySQL 5.6, the ANY_VALUE() function, which allows for aggregating non-grouped columns, is unavailable. This poses a challenge when migrating queries from MySQL 5.7 to 5.6.

MySQL Mode Modification

One solution is to modify the SQL mode for the production environment. By setting the ONLY_FULL_GROUP_BY mode to empty, it will temporarily disable the restriction on aggregating non-grouped columns.

SET SESSION sql_mode = '';
/* Your query here */
SET SESSION sql_mode = @mode;

Alternative Aggregation Methods

However, it is important to note that the query in question might not be ideal. It returns a random row from the "images" table for each country.

A more reliable approach is to select a specific image row based on a criterion, such as the "id" column.

SELECT c.id, c.name, i.*
FROM countries c
LEFT JOIN (
  SELECT MIN(id) id, country_id
  FROM images
  GROUP BY country_id
) first ON c.id = first.country_id
LEFT JOIN images i ON first.id = i.id

This query will return one row per country with a predictable image choice. It utilizes the "id" column to select the "first" image for each country.

The above is the detailed content of How to Aggregate Non-Grouped Columns in MySQL 5.6?. For more information, please follow other related articles on the PHP Chinese website!