MySQL Stuck with Group BY and ONLY_FULL_GROUP_BY error [duplicate]

70
February 06, 2019, at 11:40 AM

This question already has an answer here:

  • SQL select only rows with max value on a column 30 answers

first I would like to say that I'm aware of the only_full_group_by restriction and I understand why it's behaving this way. And I would prefer have a fine query that desactivating this option.

SELECT campaigns.uuid, campaigns.name, coupons.name, coupons.value, coupons.product_id, coupons.uuid  FROM `campaigns`
LEFT JOIN coupons ON coupons.uuid = campaigns.coupon_id
GROUP BY coupons.product_id
ORDER BY coupons.value DESC

In my coupons table I can have several coupons related to the same product_id, but I would like to return only one coupon by product, the one with the higher value.

I'm scratching my head from 4 hours but I can't find out to do it.

Thank you for your help.

Here is the structure of coupons table (useless fields removed) :

  • uuid
  • name
  • value
  • product_id

Where I can have 3 differents row :

  • uuid1 - name1 - 1.5 - 1
  • uuid2 - name2 - 0.80 - 2
  • uuid3 - name3 - 0.90 - 1

What I would like to return is just :

  • uuid1 - name1 - 1.5 - 1
  • uuid2 - name2 - 0.80 - 2

And skip the name 3 since it's the same product ID and I only want to return the coupon with the highest value.

In fact I also have other table joins to retrieve some data but I skipped them from the example to make it clearer.

Hope it explains better what I mean.

Answer 1

the simplest way is add agregation function for the columns not involved in group by

SELECT 
  min(campaigns.uuid)
, min(campaigns.name)
, min(coupons.name)
, min(coupons.value)
, coupons.product_id
, min(coupons.uuid  )
FROM `campaigns`
LEFT JOIN coupons ON coupons.uuid = campaigns.coupon_id
GROUP BY coupons.product_id
ORDER BY coupons.value DESC 

in the versions that precede mysql 5.7 the result for aggreated columns not involved in group was unpredictable (as in you code) .. in version starting from 5.7 you can substitute the unpredictable result with a controlled b y aggregation function result

READ ALSO
Why do we have to explicitly specify the entities while creating a Room Database as the entities are already annotated?

Why do we have to explicitly specify the entities while creating a Room Database as the entities are already annotated?

While using Room Persistence Library, I wonder why we have to explicitly specify the entities while creating a RoomDatabase as the entities are already annotated with @Entity annotationI mean We could simply skip the entities attribute of the @Database...

70
Access buckets created locally using AppEngine in Android with Firebase

Access buckets created locally using AppEngine in Android with Firebase

For the local development in the java app engine web application, the local files (buckets information) are stored in the local-dbbin (localhost:8080)

40
Identifying this control for Xamarin.Forms?

Identifying this control for Xamarin.Forms?

This is the first XamarinForms app I try to create, so please bear with me

34