MySql random category limited select

Question

I'm trying to create a query that will select one item for first category, two items for second and 2 items for third category, for others just one item.

The problem is it has to be one query. I think this is possible but I just can't figure it out.

Answer 1

Sounds like you need the SQL UNION operator. As mentioned in the comments, though, we'd need to know what your table looks like to give much more help.

Answer 2

This:

SELECT *
  FROM (SELECT i.*
          FROM ITEM i
         WHERE i.categoryid = 1
         LIMIT 1),
       (SELECT i.*
          FROM ITEM i
         WHERE i.categoryid = 2
         LIMIT 2),
       (SELECT i.*
          FROM ITEM i
         WHERE i.categoryid = 3
         LIMIT 2)

...will select one item for first category, two items for second and 2 items for third category. I'd use UNION ALL over UNION, because it is faster due to it not removing duplicates. Without knowing more about the data model (tables involved), it's difficult to say what would work best.