mysql - What is the best way to select multiple random results from table with different where cases

Question

I have this query, which is nothing special but I don't think it's efficient at all. I want to select from a single table random rows with different where cases. So the result should be 1 random row with the specific type for each select like shown in the queries. Some of the "type" values can be the same but they should still return random result.

Select * from fruits where type = 1 order by RAND() limit 1
Select * from fruits where type = 1 order by RAND() limit 1
Select * from fruits where type = 3 order by RAND() limit 1
Select * from fruits where type = 4 order by RAND() limit 1
Select * from fruits where type = 4 order by RAND() limit 1

question from:https://stackoverflow.com/questions/65851095/what-is-the-best-way-to-select-multiple-random-results-from-table-with-different

Answer 1

You can use ROW_NUMBER() window function with ORDER BY RAND() to assign a random ordering for the rows of each type.
Then select the number of rows you want for each type.
This will work in MySql:

SELECT *
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY type ORDER BY RAND()) rn 
  FROM fruits 
  WHERE type IN (1, 3, 4)
) t
WHERE rn <= CASE type
  WHEN 1 THEN 2 -- 2 rows for type = 1
  WHEN 3 THEN 1 -- 1 row  for type = 3
  WHEN 4 THEN 2 -- 2 rows for type = 4
END

See a simplified demo.