Mysql Join with limit?

Question

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.

Answer 1

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.

Answer 2

A slightly modified query from this article in my blog:

• Advanced row sampling

 

SELECT  l.*
FROM    (
        SELECT  category,
                COALESCE(
                (
                SELECT  count
                FROM    mytable li
                WHERE   li.category = dlo.category
                ORDER BY
                        li.category DESC, li.count DESC, li.id DESC
                LIMIT 9, 1
                ), CAST(-1 AS DECIMAL)) AS mcount
                COALESCE(
                (
                SELECT  id
                FROM    mytable li
                WHERE   li.category = dlo.category
                ORDER BY
                        li.category DESC, li.count DESC, li.id DESC
                LIMIT 9, 1
                ), CAST(-1 AS DECIMAL)) AS mid
        FROM    (
                SELECT  DISTINCT category 
                FROM    mytable dl
                ) dlo
        ) lo, mytable  l
WHERE   l.category >= lo.category 
        AND l.category <= lo.category
        AND (l.count, l.id) >= (lo.mcout, lo.id)

You need to create a composite index on (category, count, id) for this to work efficiently.

Note the usage of l.category >= lo.category AND l.category <= lo.category instead of mere: l.category = lo.category

This is a hack to make MySQL use efficient Range check for each record