theLazyTrojan theLazyTrojan - 1 year ago 51
MySQL Question

How to get Items in order with limit in mysql

I have a table

Item ( id, 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.!9/ef83d/1

Answer Source

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.type1 as 'Type'
                 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, X
      WHERE x.rank<=2;

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