Random rows from MySql

Question

I have a table in MySql database with thousands of records. The structure is as below

Here the column "difficulty" has three possible values, which are A, B, and C.

Question is I need to retrieve 10 random rows from the entire table in which 4 rows are of one of difficulty type A, 4 rows are of difficulty type B and the remaining 2 rows of difficulty type C.

I want to achieve this with a single call. I am not good in writing SQL queries and looking for some guidance.

Thanks,

One option to make this task simpler: Write three separate queries for the rows with type A, B and C and use UNION ALL to put the results together — Jonas Metzler, Jul 27 '23 at 11:37

score 1 · Answer 1 · edited Jul 27 '23 at 12:56

1

Not sure if I fully understand your problem, but I think this is what you want.

(
  SELECT * FROM your_table
  WHERE difficulty = 'A'
  ORDER BY RAND()
  LIMIT 4
)
UNION ALL
(
  SELECT * FROM your_table
  WHERE difficulty = 'B'
  ORDER BY RAND()
  LIMIT 4
)
UNION ALL
(
  SELECT * FROM your_table
  WHERE difficulty = 'C'
  ORDER BY RAND()
  LIMIT 2
)

This consists of three separate SELECT statements connected by the UNION. Each SELECT statement retrieves the specified number of random rows for the corresponding difficulty type.

edited Jul 27 '23 at 12:56

The Impaler

45,731
9
39
76

answered Jul 27 '23 at 11:54

Polarcode

309
6

1

You should use UNION ALL here rather than UNION because UNION slows down the query and has no affect here. But then that's just a copy of [Aleti's answer](https://stackoverflow.com/a/76779414/18794826) – Jonas Metzler Jul 27 '23 at 11:57
1

@JonasMetzler -Agreed UNION ALL is faster. – Polarcode Jul 27 '23 at 12:08

Aleti · Accepted Answer · 2023-07-28T06:30:39.027

-2

Try something like this:

(
  SELECT * FROM table
  WHERE difficulty='A'
  ORDER BY RAND()
  LIMIT 4
)
UNION ALL
(
  SELECT * FROM table
  WHERE difficulty='B'
  ORDER BY RAND()
  LIMIT 4
)
UNION ALL
(
  SELECT * FROM table
  WHERE difficulty='C'
  ORDER BY RAND()
  LIMIT 2
);

edited Jul 28 '23 at 06:30

answered Jul 27 '23 at 11:46

Aleti

61
2
5

3

This syntax is not valid in MySQL, you need to add brackets around the three queries. See [Combine LIMIT and UNION in MySQL](https://stackoverflow.com/questions/1415328/combining-union-and-limit-operations-in-mysql-query) – Jonas Metzler Jul 27 '23 at 11:55
2

Here a fiddle with the valid syntax: https://dbfiddle.uk/ToH2LE91, you could edit your question and add this to improve your answer (since generally, your idea is correct) – Jonas Metzler Jul 27 '23 at 11:59
Yes you are correct, but from what i understand from the question all i tried to do is set him on the right path – Aleti Jul 28 '23 at 05:59
So what prevents you from [editing and improving your answer](https://meta.stackexchange.com/questions/40318/best-practices-when-editing-your-own-answer)? – Jonas Metzler Jul 28 '23 at 06:18

Random rows from MySql

2 Answers2