Randomly selecting a limited number of records with two conditions in MySQL

Question

I have a MySQL table with a column called "priority". The column can have two values: high or low. I want to select 8 records from the table at random, but I want 6 of them to be high priority, and 2 of them to be low priority. If possibly, I would like to do it in one SQL statement. Is there any way to do two LIMITS in one query based on this kind of criteria?

Answer 1

Use a union, so you have two select statements in one, basically.

Answer 2

SELECT t1.*
  FROM table t1
 WHERE priority = 'high'
 ORDER BY rand() Limit 8
UNION ALL
SELECT t1.*
  FROM table t1
 WHERE priority = 'low'
 ORDER BY rand() Limit 2

Answer 3

Use a union:

SELECT * FROM table WHERE priority = 'high' ORDER BY RAND() LIMIT 8 
UNION ALL
SELECT * FROM table WHERE priority = 'low' ORDER BY RAND() LIMIT 2

Answer 4

For those of you who came into the same problem I did, I got an error that said "Incorrect usage of UNION and ORDER BY ". Then I found this page that tells you how to fix it. All you do is surround the query by parentheses. Thanks for helping out!