How to get Items in order with limit in mysql

Question

I have a table Item ( id, itemType). itemType can be from 1-5.

I want to retrieve 2 items of each type using mysql.

I tried

select * from `item` ORDER BY `itemType` limit 2 

which gives me 2 items order by type but I want 2 items of each type.

http://sqlfiddle.com/#!9/ef83d/1

Answer 1

You can use below query, even i did not check it with data as I dont have sample data so if you get any issue then you can create a sqlfiddle, so that I can customize query as per you in #sql

SELECT x.id,x.type1 as 'Type'
FROM (SELECT t.*,
               CASE 
                 WHEN @type != t.type THEN @rownum := 1 
                 ELSE @rownum := @rownum + 1 
               END AS rank,
               @type := t.type AS 'type1'
          FROM item t
          JOIN (SELECT @rownum := NULL, @type := '') r     
      ORDER BY t.type,t.id) X
      WHERE x.rank<=2;

Even you can ordering based on top price or any other field.