Geek Answers Handbook

MySQL: how to get x number of results per group

Possible duplicate:
mysql: using LIMIT inside GROUP BY to get N results for each group?

I have two tables:

  • Items
  • Category

Each item belongs to a category. What I want to do is select 5 items for each category, but only 20 pieces.

SELECT item_id, item_name, items.catid FROM items, categories WHERE items.catid = categories.catid GROUP BY items.catid LIMIT 0,5 //5 per category group

Edit: if there are more than 5 elements for a category, they need to be sorted by item_id (numeric value)

+7
source share
2 answers

Try this query -

 SELECT item_id, item_name, catid FROM (SELECT t1.*, COUNT(*) cnt FROM items t1 LEFT JOIN items t2 ON t2.catid = t1.catid AND t2.item_id <= t1.item_id GROUP BY t1.catid, t1.item_id ) t WHERE cnt < 6 -- LIMIT 20

It will display the first 5 items for each category. Uncomment the LIMIT 20 if you need to. Attach the Categories table if you need to.

+5
source 
 SELECT item_id, item_name, catid FROM ( SELECT item_id, item_name, catid, CASE WHEN @catid != catid THEN (@rownum := 1) + !(@catid := catid) ELSE @rownum := $rownum + 1 AS rownum FROM ( SELECT item_id, item_name, i.catid FROM items AS i INNER JOIN categories AS c ON i.catid = c.catid ORDER BY i.catid ASC) AS h, (SELECT @catid := NULL, @rownum := NULL) AS var HAVING rownum <= 5 LIMIT 20) AS h2 ORDER BY item_id

Devart's solution looks right, but I'm a little pickled. Mine cannot do much more attention for others, but it is with me.

EDIT: Understand the Devart solution now, and actually it's pretty brilliant. :)

0
source

More articles:


All Articles