Mysql Join with limit?

mysql

Solution

select a.* from t a where 10 >  (
   select count(*) from t b 
   where b.category=a.category 
   and b.count<a.count
)

I think this is what you need.

Problem

I have a table with category, product and count. All integers. I'm looking for the most efficient query that will give me the top 10 products (highest count) for each category. I've tried several subselects and joins but couldn't figure out how to do it in a single query. Thanks for your help.

Original source