MySQL – Order by Rand with Specific Value Constraints

MySQLrandom

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.

Best Answer

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.

Related Question