MySQL order by rand but result must exist different specific value 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 &lt;-- One from category 3
4   |      1     |  D &lt;-- One from category 1
7   |      2     |  G &lt;-- One from category 2
5   |      3     |  E &lt;-- 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.
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 &gt; 1, RAND()
LIMIT ? ;


fiddle

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