MySQL: how to get x number of results per grouping

greatest-n-per-group, group-by, mysql, sql

Solution

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 show first 5 items per category. Uncomment `LIMIT 20` if you need. Join `Categories` table if you need.

Problem

Possible Duplicate: mysql: Using LIMIT within GROUP BY to get N results per group? I have a two tables: - Items - Categories Each item belongs to a category. What I want to do is select 5 items per category but say 20 items in total. ``` 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 items per category - they should be ordered by the item_id (numeric value)

Original source

Related problems