0

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.

0
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.

1
  • Thank you, it working nice. – Topup Mar 25 '20 at 16:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.