Mysql Join the Limit?

Question

I have a table with a category, product and counter. All integers. I am looking for the most effective query that will give me the top 10 products (highest score) for each category.

I tried several subqueries and connections, but could not figure out how to do this in a single query. Thank you for your help.

Answer 1

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

I think this is what you need.

Answer 2

A slightly modified request from this article on my blog:

• Extended row selection

 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) so that it works efficiently.

Note the use of l.category >= lo.category AND l.category <= lo.category instead of simple: l.category = lo.category

This hack to make MySQL efficient Range check for each record

Answer 3

This article addresses your issue, I think.

Basically, this suggests that if your table is small, you can make a join with an explicit inequality, for example:

 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; 

This is an expensive operation, but for a small table you should be fine. If you have a large table, you should forget about its execution with one query and implement a different approach to the solution.

Answer 4

 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; 

Answer 5

 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'