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