Is there ANY_VALUE capability for mysql 5.6?

by

You’re misusing the notorious nonstandard MySQL extension to GROUP BY. Standard SQL will always reject your query, because you’re mentioning columns that aren’t aggregates and aren’t mentioned in GROUP BY. In your dev system you’re trying to work around that with ANY_VALUE().

In production, you can turn off the ONLY_FULL_GROUP_BY MySQL Mode. Try doing this:

  SET @mode := @@SESSION.sql_mode;
  SET SESSION sql_mode="";
  /* your query here */
  SET SESSION sql_mode = @mode;

This will allow MySQL to accept your query.

But look, your query isn’t really correct. When you can persuade it to run, it returns a randomly chosen row from the images table. That sort of indeterminacy often causes confusion for users and your tech support crew.

Why not make the query better, so it chooses a particular image. If your images table has an autoincrement id column you can do this to select the “first” image.

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

That will return one row per country with a predictable image shown.

More Related Contents:

  1. Error related to only_full_group_by when executing a query in MySql
  2. SELECT list is not in GROUP BY clause and contains nonaggregated column …. incompatible with sql_mode=only_full_group_by
  3. MySQL “Group By” and “Order By”
  4. SQL Query to get column values that correspond with MAX value of another column?
  5. mysql count group by having
  6. Using LIMIT within GROUP BY to get N results per group?
  7. Group by clause in mySQL and postgreSQL, why the error in postgreSQL?
  8. Strange duplicate behavior from GROUP_CONCAT of two LEFT JOINs of GROUP_BYs
  9. Retrieving the last record in each group – MySQL
  10. What’s faster, SELECT DISTINCT or GROUP BY in MySQL?
  11. MySQL – Selecting a Column not in Group By
  12. How to group by week in MySQL?
  13. MySQL : isn’t in GROUP BY
  14. Mysql query to dynamically convert rows to columns
  15. SELECT / GROUP BY – segments of time (10 seconds, 30 seconds, etc)
  16. Select the 3 most recent records where the values of one column are distinct
  17. Group mysql query by 15 min intervals
  18. In SQL, how to select the top 2 rows for each group
  19. #1055 – Expression of SELECT list is not in GROUP BY clause and contains nonaggregated column this is incompatible with sql_mode=only_full_group_by
  20. Returning the ‘last’ row of each ‘group by’ in MySQL
  21. Is it possible to GROUP BY multiple columns using MySQL?
  22. Using Count to find the number of occurrences
  23. MySQL: How do I join same table multiple times?
  24. Converting SELECT DISTINCT ON queries from Postgresql to MySQL
  25. GROUP BY – do not group NULL
  26. mysql select sum group by date
  27. MySQL Group By Hours
  28. MySQL cumulative sum grouped by date
  29. group by range in mysql
  30. SQL query with avg and group by

Leave a Comment