0

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.

Fabrice Lefloch
  • 409
  • 4
  • 16
  • "And I would prefer have a fine query that desactivating this option." Why? You are aware you are telling MySQL something like "give me the data and i don't care if the data is correct" when you disable sql_mode ONLY_FULL_GROUP_BY? .. i advice you to provide example data and expected results [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) – Raymond Nijland Feb 05 '19 at 15:24
  • Grouping by a column from a LEFT JOINed table looks pretty wrong. Are you sure you know what you are doing? – Paul Spiegel Feb 05 '19 at 15:28

1 Answers1

0

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

ScaisEdge
  • 131,976
  • 10
  • 91
  • 107