MYSQL How to fetch the first row before group by-CodePudding

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?

CodePudding user response：

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;