Question 217175 in Mysql Question posted in
The official MySQL documentation can be found here.

Random rows from MySql

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

enter image description here

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,

Tags:

3

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
  3. 0

    Multiple select statements in Single query
    https://www.petefreitag.com/item/466.cfm

    @Aleti and @Polarcode ‘s answers are interesting, but it seems they will just "randomly mix" the datas inside the RESULTING tableS.
    It feels more logical to me to mix your original table before querying on it, creating the mixed table a WITH statement at the begining, and querying on it.

    I guess what you are looking for might look like this :

    WITH rand_order AS (SELECT * FROM table ORDER BY RAND())
SELECT * FROM rand_order WHERE difficulty="A" LIMIT 4 UNION
SELECT * FROM rand_order WHERE difficulty="B" LIMIT 4 UNION
SELECT * FROM rand_order WHERE difficulty="C" LIMIT 2
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search