MySQL order by rand but result must exist different specific value

Question

I'm trying the get random values from SQL but I want results must have minimum one specific value from one column, how can I do it?

For example;

TABLE;

id | category | name
----------------------------
1 | 1 | A
2 | 2 | B
3 | 3 | C
4 | 1 | D
5 | 3 | E
6 | 3 | F
7 | 2 | G
8 | 3 | H

When I query random four data with category id, results not have all different categories, sometimes result coming like this;

SELECT * FROM table WHERE category IN (1,2,3) ORDER BY RAND LIMIT 4
id | category | name
----------------------------
1 | 1 | A
4 | 1 | D
3 | 3 | C
8 | 3 | H

Category 2 is missing in this example but I want every category must in results like that;

id | category | name
----------------------------
3 | 3 | C <-- One from category 3
4 | 1 | D <-- One from category 1
7 | 2 | G <-- One from category 2
5 | 3 | E <-- All categories ok, it can be random from any of them

Sorry for my english, I hope I could explain what I wanted. Results must have minimum one row from where clause "category IN (X,Y,Z)".

Thank you.

Answer 1

WITH 
cte AS ( SELECT id, category, name,
 ROW_NUMBER() OVER (PARTITION BY category ORDER BY RAND()) rn
 FROM test )
SELECT id, category, name
FROM cte
ORDER BY rn > 1, RAND()
LIMIT ? ;

fiddle

For MySQL version below 8 calculate rn in subquery using user-defined variables.

• Topup: Thank you, it working nice.