SELECT list is not in GROUP BY clause and contains nonaggregated column … incompatible with sql_mode=only_full_group_by

Question

I have data that looks like this in RESOURCE TABLE

id  name  weight  key
1   res1   12    1
2   res2   12    1
3   res3   13    2
4   res4   13    2

I have the following query

SELECT
resource.id,
resource.name,
resource.weight,

from resource join 
inventory on resource.weight = inventory.weight
where inventory.key = ?
GROUP BY resource.weight;

I would like to get this data, I would like group by weight so I only have all unique weight values

id  name  weight  key
1   res1   12    1
4   res4   13    2

I'm getting this error however

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'RESOURCE.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

So when I add the id to the Group by

GROUP BY resource.value, resource.name, resource.id;

It works but now I no longer get the unique weight values, it adds all the rows.

How can I achieve this without turning off sql_mode=only_full_group by?

Answer 1

you can't use group by without aggregation function for reduce the number of rows ..

for this you should choice a criteria for aggreagation eg min

  select m.* from my_table  m 
  inner join ( 
  select min(id) min_id,  weight
  from my_table 
  group by weight
  ) t on t.min_id =  m.id 

and join the resulting subquery for your table