Question :
how do i get rows for each month which contain the store with the highest number of purchases?
my code is:
SELECT DATE_FORMAT(buyDate, '%M') as month, count(id) as numPurchases, store
from purchase, customerpurchases
where purchase.id = customerpurchases.purchaseid
group by month, store;
Answer :
SELECT t1.*
FROM purchase t1
JOIN ( SELECT t2.`month`, MAX(t2.numPurchases) numPurchases
FROM purchase t2
GROUP BY t2.`month` ) t3
ON t1.`month` = t3.`month`
AND t1.numPurchases = t3.numPurchases