'MYSQL How to fetch the first row before group by

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: enter image description here

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: Result 2

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?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source