MySQL: how to get x number of results per grouping

Question

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)

When there are more than 5 items in a category, what rules should be used to determine ***which*** 5 should be returned? — MatBailie, Nov 13 '12 at 11:07
@Dems, Good Question: if there are more than 5 items per category - they should be ordered by the item_id (numeric value) and pick the top 5 — KB., Nov 13 '12 at 11:09
I think this is what you want to achieve. [http://stackoverflow.com/questions/2129693/mysql-using-limit-within-group-by-to-get-n-results-per-group][1] [1]: http://stackoverflow.com/questions/2129693/mysql-using-limit-within-group-by-to-get-n-results-per-group — Abdul Haseeb, Nov 13 '12 at 11:31
One of the thousand+ questions about greatest n per group. http://stackoverflow.com/search?q=greatest+n+per+group — fancyPants, Nov 13 '12 at 11:33

score 5 · Accepted Answer · answered Nov 13 '12 at 11:24

5

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.

answered Nov 13 '12 at 11:24

Devart

119,203
23
166
186

reinvented the wheel, and yet not very well, see the elegant six-liner here: http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/ – Tomas Jun 08 '13 at 17:59
The page that @Tomas shares a link to talks about choosing the top 'x' per group. However, there is a link on that page that talks about choosing 'x' number from each group. http://www.xaprb.com/blog/2006/12/02/how-to-number-rows-in-mysql/ – donL Oct 09 '13 at 14:16
It is working for me but could not understand how. Could you explain please? – Sandeepan Nath Sep 23 '16 at 10:55

score 0 · Answer 2 · answered Nov 13 '12 at 12:07

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 have a bit of a pickle understanding it fully. Mine may not make much more sence to others, but it does to me.

EDIT: Understand Devart's solution now, and it is quite brilliant, actually. :)

MySQL: how to get x number of results per grouping

2 Answers2

Linked