Get highest offer price with correct offer id in group by and join query

Question

I am joining product table with offer table using product id, my requirement is get highest offer(%) of that product, at the same time I want to make group by clause for product id because I don't want to repeat product but it is showing wrong offer id because joining query is first performed based on product id :

product table:

id   name
1     abc
2     xyz

offer table

id   to_date        product_id   offer
1   2020-12-18       1            30%
2   2020-12-18       1            40%
3   2020-12-18       2            30%
4   2020-12-18       2            40%

Query :

SELECT product_id,product.name,o.id as offer_id,o.to_date, max(o.offer)offer_price
FROM products 
  LEFT JOIN offers o ON o.product_id=product.id
GROUP BY product.id

Output with issue( wrong offer id):

offer_id product_id  to_date       offer_price 
 1           1        2020-12-18      40%
 3           2        2020-12-18      40%

Expected output(correct offer id)

offer_id product_id  to_date       offer_price 
 2           1        2020-12-18      40%
 4           2        2020-12-18      40%

Answer 1

You can use window functions:

select o.*, p.name as product_name
from product p
left join (
    select o.*,
        row_number() over(partition by product_id order by offer desc) rn
    from offer o
) o on o.product_id = p.id and o.rn = 1

row_number() ranks records having the same product_id by descending offer - so the row with the greatest offer for each product gets rank 1. You can then use that information for filtering.

This requiers MySQL 8.0. In earlier versions, one alternative uses a correlated subquery to filter on the greatest offer per product:

select o.*, p.name as product_name
from product p
left join offer o 
    on  o.product_id = p.id
    and o.offer = (select max(o1.offer) from offer o1 where o1.product_id = p.id)