MySQL How to order rows before grouping them?

Question

I'm trying to return just 1 row for every size, brand and kind but sometimes there are multiple rows with the exact same values except enddate.

I want to return the row with the highest enddate (Order by enddate desc) doesn't work.

SELECT 
    promotion.id,
    promotion.uid,
    promotion.promotion_id,
    promotion.url,
    promotion.shop_id,
    promotion.brand_kind_id,
    promotion.before_price,
    promotion.after_price,
    promotion.brand_id,
    promotion.brand_package_id, 
    promotion.brand_size, 
    promotion.amount, 
    promotion.begindate,
    promotion.enddate

FROM 
    `promotion`     

WHERE 
    AND (promotion.url is not null AND promotion.url != '')

GROUP BY 
    promotion.brand_id,
    promotion.brand_kind_id,
    promotion.brand_size

ORDER BY 
    promotion.enddate DESC

score 0 · Accepted Answer · answered Oct 31 '18 at 15:37

If the specific problem is that some rows have a different end date, I would recommend removing end date from your select string. This will mean that rows with different end dates will not be considered.

Another option might be to select the MAX end date, you could try this:

SELECT 
    promotion.id,
    promotion.uid,
    promotion.promotion_id,
    promotion.url,
    promotion.shop_id,
    promotion.brand_kind_id,
    promotion.before_price,
    promotion.after_price,
    promotion.brand_id,
    promotion.brand_package_id, 
    promotion.brand_size, 
    promotion.amount, 
    promotion.begindate,
    MAX(promotion.enddate)
FROM 
    `promotion`     
WHERE 
    AND (promotion.url is not null AND promotion.url != '')
GROUP BY 
    promotion.brand_id,
    promotion.brand_kind_id,
    promotion.brand_size
ORDER BY 
    promotion.enddate DESC

However I would only recommend this method if you intend to use the end date as data.

MySQL How to order rows before grouping them?

1 Answers1