MYSQL How to fetch the first row before group by

Question

I am trying to fetch products grouped by it's group code, but sorted by it's selling price.

When I run this query:

SELECT p.id, p.base_model, p.group_code, p.retail_price, p.selling_price, option_name
FROM product p
LEFT JOIN product_category pc ON pc.product_id = p.id
LEFT JOIN product_filter_value pfv1 ON p.id = pfv1.product_id
WHERE (pc.category_id = ?
  AND (p.active = ?)
  AND (pfv1.filter_id = ?)
  AND (pfv1.filter_value_id IN (?))
  AND (p.type = "shop")
  AND (p.group_code = ?)
ORDER BY IF(p.combideal_active = 1, p.combideal_price, p.selling_price) asc

I am getting this result:

When I add a group by and run this query:

SELECT p.id, p.base_model, p.group_code, p.retail_price, p.selling_price, option_name
FROM product p
    LEFT JOIN product_category pc ON pc.product_id = p.id
    LEFT JOIN product_filter_value pfv1 ON p.id = pfv1.product_id
WHERE (pc.category_id = ?)
  AND (p.active = ?)
  AND (pfv1.filter_id = ?)
  AND (pfv1.filter_value_id IN (?))
  AND (p.type = "shop")
  AND (p.group_code = ?)
GROUP BY p.group_code
ORDER BY IF(p.combideal_active = 1, p.combideal_price, p.selling_price) asc

I am getting this result:

You can see, this is not the cheapest product from the first query. Can anyone help me out to getting the cheapest one as the result?

Answer 1

This kind of operations are easy to perform in MySQL 8.x, using window functions. But there is no such thing in MySQL 5.7.

You could however use group_concat like this:

select p.*
from   product p
inner join (
    select   p.group_code,
             cast(substring_index(group_concat(p.id 
                 order by if(p.combideal_active = 1, p.combideal_price, p.selling_price) 
                 separator ','
             ), ',', 1) as unsigned) id
    from     product p
    left join product_category pc ON pc.product_id = p.id
    left join product_filter_value pfv1 ON p.id = pfv1.product_id
    where /* ...etc... */
    group by p.group_code
) grouped on  p.id = grouped.id;