CODEWITHSUNDEEP
sqlmysql
Luka
Luka

Reputation: 321

MySql random category limited select

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.

Upvotes: 0

Views: 161

Answers (2)

OMG Ponies
OMG Ponies

Reputation: 332591

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.

Upvotes: 0

David
David

Reputation: 2871

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.

Upvotes: 1

Related Questions