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

This article addresses your problem I think.

Basically, it says that if your table is small, you can do a self inequality join, like this:

SELECT t1.*, COUNT(*) AS countRank
FROM tbl AS t1
JOIN tbl AS t2 ON t1.category=t2.category AND t1.count <= t2.count
GROUP BY t1.category, t1.count
HAVING countRank <= 10
ORDER BY category,count DESC;

It's an expensive operation, but for a small table you should be fine. If you have a large table, you should forget about doing it with one query and implement a different approach to the solution.

Answer 2

select a.* from `table` a where a.product in (
    select b.product from `table` b 
    where b.category=a.category 
    order by b.count desc 
    limit 10
)

I think this is a good way, but mysql returns:

 MySQL 返回：文档
#1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Answer 3

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 4

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

Answer 5

SET @row = 0;
SET @category = 0;
 
SELECT top.*
FROM (
  SELECT IF(@category = p.cId, @row := @row + 1, @row := 1) rowNumber,
    (@category := p.cId) categoryId,
    p.pId
  FROM (
    SELECT c.cId,
      c.pId
    FROM prod pr
      INNER JOIN cat_prod c ON c.pId = pr.id
    GROUP BY c.cId, c.pId
    ) p
  ) top
HAVING top.rowNumber < 4;