SELECT * FROM recipes_data group by recipe_name,recipe_version
Above query, executed in Workbench (MySQL) by using the below solution, work perfectly.
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
But the same query does not execute in Database Query Browser ignition. Its showing below error:
Any idea how to fix this issue?
What exactly are you trying to retrieve from the database? If you want the count of versions then try this.
recipe_name AS 'Recipe',
COUNT(recipe_version) AS 'Versions'
GROUP BY recipe_name, recipe_version
(Note how a couple of line breaks make it much more readable.)
You're selecting things that are not in your groups, and you need to provide some information about what to do with those. Which means an aggregation function -
What exactly are you trying to do ?
What @pascal.fragnoud said. If your SQL is set to
sql_mode=only_full_group_by then every column that is not using an aggregate function has to in your GROUP BY clause.
More info - sql - Error related to only_full_group_by when executing a query in MySql - Stack Overflow
Seems that you may have already seen this thread though given your attempt to try to change the sql mode. I would just fix the query though instead of changing the mode. It may be a little annoying and more verbose but it will also be more explicit about what is being done. If you truly don't care about what the value of a certain column is in when grouped, there is the
Generally, using any
* wildcard in a SELECT list is fundamentally incompatible with
GROUP BY. Grouping requires explicit grouping output columns and aggregate output columns, and nothing else, in every SQL dialect I know.