samedi 20 février 2021

MySQL select random row that has a count of 3

I want to select a random row from MySQL, but that row has to appear three times in the table. Believe it or not, but I actually have a good reason for wanting to do this! Suppose that I have a table with the following data.

word    wordType
house   noun
house   noun
house   noun
car     noun
car     noun
car     noun
floor   noun
floor   noun

I want my select statement to pick one instance, randomly, where wordType = 'noun' but the word has to appear three times in the table. So the results may be house one time and may be car the next, but it is random so it could be the same result multiple times, but it will never pick floor because floor only appears in the table twice.

here is some sample output

myquery = house
myquery = house
myquery = car

I tried this, but it did not work

SELECT  SUBSTRING_INDEX(GROUP_CONCAT(word ORDER BY RAND()),',', 1)  AS word
FROM myTable
WHERE wordType ='noun'
HAVING COUNT(*)= 3;



Aucun commentaire:

Enregistrer un commentaire