How to select 3 of each results for given catids in mysql

Question

This may be a tough one for you all, if it is even possible in mysql.
I have a table that stores id, catid, product_name
What i would like to do is select 3 records for each catid then display the result with each 3 sets of records first.
So if i have a catid of 4, 7 then I would like to display 3 results for (4) then 3 results for (7).

Any help is greatly appreciated. Thanks.

Answer 1

Use:

SELECT x.id,
       x.catid,
       x.product_name
  FROM (SELECT t.id,
               t.catid,
               t.product_name,
               CASE 
                 WHEN @catid = t.catid THEN @rownum := @rownum + 1
                 ELSE @rownum := 1
               END AS rank,
               @catid := t.catid
          FROM YOUR_TABLE t
          JOIN (SELECT @rownum := 0, @catid := -1) r
      ORDER BY t.catid, t.product_name) x
WHERE x.rank <= 3

Mind that I've seen weird behavior from MyISAM tables -- InnoDB tables are more consistent.