MYSQL select query for most frequent product?

Question

I need help in finding out how to display only the most frequent product for each branch.

This applies to each branch in the DB.

PS: This is done in one table (other tables aren't necessary for this performance), and only the two attributes mentioned are required.

Answer 1

Based on Sean's DB sample, I've created query, which do the job, but, please, note what the way you're going in your application looks resource expensive. Try thinking about another way to discover most frequent products, not a DB query.

SELECT 
    a.branch_id, `branch_product` , COUNT(branch_product) AS cnt , b.mx
FROM 
    branch_products a 
LEFT JOIN 
    (SELECT 
        branch_id, MAX(cnt) AS mx 
     FROM 
        (SELECT branch_id, COUNT(branch_product) AS cnt FROM branch_products GROUP BY branch_id, branch_product ) AS maxes 
     GROUP BY branch_id) b 
ON a.branch_id = b.branch_id
GROUP BY branch_id, branch_product
HAVING cnt=mx