CODEWITHSUNDEEP
phpmysqlsql
emreaydin
emreaydin

Reputation: 13

How to limit multiple numbers by filtering multiple columns on MySql?

I would like to get 50 questions from 4 different categories from the database. I want a different number of questions from each of the 4 different categories; my result set must contain first category 12 questions, second category 20 questions, third 10, and fourth category 8 questions. In total 50 question from my question table. I used the LIMIT function for this. But when I have more than one column, I'm confused.

This is my table:

 | category_id| question-text| Col3 |
 |------------|--------------|------|
 |    1       |  sample      |  ieb |
 |    2       |  sample      |  aem |
 |    3       |  sample      |  atd |
 |    4       |  sample      |  oui |
 |    1       |  sample      |  ieb |
 |    2       |  sample      |  aem |
 |    3       |  sample      |  atd |
 |    4       |  sample      |  oui |
 |    1       |  sample      |  ieb |
 |    2       |  sample      |  aem |
 |    3       |  sample      |  atd |
 |    4       |  sample      |  oui |
 |    1       |  sample      |  ieb |
 |    2       |  sample      |  aem |
 |    3       |  sample      |  atd |
 |    4       |  sample      |  oui |

And I need this result: 5 questions from category # 1. 3 questions from category # 2. 2 questions from category # 3. 7 questions from category # 4.

Upvotes: 1

Views: 876

Answers (1)

Nick
Nick

Reputation: 147146

You can use a UNION query to get all your questions at once:

(SELECT * FROM question WHERE categori_id=1 ORDER BY RAND() LIMIT 12)
UNION
(SELECT * FROM question WHERE categori_id=2 ORDER BY RAND() LIMIT 20)
UNION
(SELECT * FROM question WHERE categori_id=3 ORDER BY RAND() LIMIT 10)
UNION
(SELECT * FROM question WHERE categori_id=4 ORDER BY RAND() LIMIT 8)

Upvotes: 2

Related Questions