MySQL select random row that has a count of 3

Question

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;

Answer 1

You can subselect the words with count(*) bigger than 2 and then pick a random selection in the outer query

Schema (MySQL v8.0)

CREATE TABLE wordtable (
  `word` VARCHAR(5),
  `wordType` VARCHAR(4)
);

INSERT INTO wordtable
  (`word`, `wordType`)
VALUES
  ('house', 'noun'),
  ('house', 'noun'),
  ('house', 'noun'),
  ('car', 'noun'),
  ('car', 'noun'),
  ('car', 'noun'),
  ('floor', 'noun'),
  ('floor', 'noun');

---

Query #1

SELECT 
    `word`
FROM wordtable
WHERE `word` in (
SELECT 
    `word`
FROM wordtable
  WHERE `wordType` = 'noun'
GROUP BY `word`
HAVING count(`word`) >= 3)
ORDER BY RAND()
LIMIT 3;

word
car
house
car

---

View on DB Fiddle